- Create a new ADO.NET connection manager that uses the Microsoft OLE DB Provider for Analysis Service.
-
For each task in the process, you will have the appropriate connection manager available in the drop-down without the need to create any on-the-fly. Use a real connection in design time so that the metadata plays nice.Ĭreating the connection managers now isn't required, but it makes it easier for later. XMLAScript | Package or Foreach level | String Please note that this approach can be expanded upon to have the entire ConnectionString be dynamic and dictated by the calling SQL Agent job step if necessary.
Set the Expression property as follows: Data Source="+ +" Provider=MSOLAP.4 Integrated Security=SSPI Set the EvaluateAsExpression property to true. This variable will be used to build the ConnectionString.ĬonnectionString | Package level | String Intended for use in the Set Values tab of the SQL Agent job if more than one instance will be backed up.InstanceForDatasource | Package level | String An Execute Script Task that uses the Analysis Management Objects (AMO).īackupDir | Package level or Foreach level | StringĭatabaseId | Package level or Foreach level | StringĭatabaseName | Package level or Foreach level | String.One dynamic SSIS package that is called in each step of the job.A step for the development server, the qa server, and for production). A SQL Agent job with a step for each instance that needs backed up (i.e.For this solution, you will use a combination of: There are probably a few ways to do it, and here is a fairly straightforward example.