SQL Server BI Gold Medal Winner - Using a View to Mask Multi-Server SSIS Package Configurations

  • Article

Jason won the SQL BI Guru gold medal for October! Here's his winning article...

 

Using a View to Mask Multi-Server SSIS Package Configurations

By Jason Brugger
December 8, 2013

SSIS Package Configurations allows property values which are particular to a given SQL Server instance to be customized for that instance without modification to the underlying package. To quote SQL BOL, "SQL Server Integration Services provides package configurations that you can use to update the values of [package] properties at run time." If unfamiliar with package configurations...

Here's an excerpt:

Next, complete the Package Configuration Wizard and specify the view as the configuration source:

  1. In SSDT, under package properties, click Configurations.
  2. In the Package Configurations Organizer, check Enable package configurations, and click Add.
  3. Under Configuration type, select SQL Server.
  4. Under Connection, click New to create a new Connection manager if a connection manager of type "Native OLE DB\SQL Server Native Client 11.0" doesn't already exist. Select the database that hosts the configuration table and view.
  5. Under Configuration table, select the view (not the table) created above.
  6. Under Configuration filter, choose any name that will uniquely distinguish this package from other packages. Avoid any server-specific references.
  7. Click Next and complete the wizard.

 

 

Read the article here: Using a View to Mask Multi-Server SSIS Package Configurations 

 

   - Ninja Ed