SSIS package Configuration

SQL Server Integration Services SSIS Package Configuration Written By: Ray Barley Problem As we develop, test and deploy SSIS packages, we need to specify different parameter values for things like database connection strings, file and folder paths, etc. without having to edit the individual SSIS packages to make these changes. how can we take advantage of the configuration capabilities that SSIS provides, the SQL Server package configuration, to accomplish our goal? Solution SSIS provides several options for handling package configuration values such as environment variables, XML files, registry settings, and a table in a SQL Server database. in each case you are storing and maintaining configuration parameter values outside of the SSIS package then using those stored values when executing the package. there are several advantages to using SQL Server package configuration over the other options such: Storing package configuration values in a SQL Server database rather than on the file system or in the registry allows us to include this data in our standard SQL Server Backup and Restore plans. Changing the configuration data can be done with simple T-SQL commands such as INSERT, UPDATE and DELETE. DBAs are usually more comfortable working with SQL Server tables than XML files or registry settings. Before we walk through the steps to setup SQL Server package configuration in an SSIS package, there are two preliminary things that need to be done. first, you need to decide on the database server and database name to store the configuration data; for our example we will use the local server and SSISConfig as the database name. while not a requirement to store the SQL Server package configuration in its own database, it is usually a good idea to do so. second, you shocould setup an environment variable that holds the database connection string to the package configuration database on the machine (s) where your SSIS package will run. we will configure the SSIS package to get the database connection string for the package configuration database from the environment variable, allowing us the flexibility to change the configuration database as the SSIS package goes through the development, testing and deployment phases. To setup the environment variable, use the standard Windows Control Panel System Applet and create a system environment variable. for example, use ssis_config_db as the environment variable and assign this ole db database connection string as its value: Data Source = localhost; Initial Catalog = SSISConfig; Provider = SQLNCLI.1; Integrated Security = SSPI; Auto Translate = False; Now we are ready to setup an SSIS package to use SQL server package configuration. To begin, open business intelligence developer Studio (bids) or Visual Studio. We will perform the following steps: Create a new Integration Services project Add a new SSIS package to the project; the steps following are saved med in this package Add Connection Managers for the SSISConfig and AdventureWorks databases (or any other database of your choice) Add an environment variable package configuration to assign the SSISConfig database ConnectionString property Add a SQL Server package configuration to assign the AdventureWorks database ConnectionString property The detailed steps with screen shots follow: 1. Create a new project; click File, New Project:   2. Select Business Intelligence Projects as the Project Type, Integration Services Project as the template; fill in the Name and Location (change as necessary ):   3. Add a new SSIS package to the project; right click SSIS Packages in the Solution Explorer, then select New SSIS Package from the popup menu.   4. Add the Connection Manager for the SQL Package Configuration database; right click in the Connection Managers area of the new package and select New OLE DB Connection   5. Configure the Connection Manager to point to the SSISConfig database:   6. repeat steps 4 and 5 to add another Connection Manager; point to the AdventureWorks database (or the database of your choice ); we will use SQL Server Package Configuration to assign the ConnectionString property of this database.   7. Right click on the design surface and select Package invocations from the popup menu:   8. Click Enable package configurations then click Add:   9. Add the SSIS_CONFIG_DB environment variable configuration then click Next:   10. Set the ConnectionString property of the SSISConfig Connection Manager to the value of the environment variable:   11. Name the environment variable configuration:   12. add a SQL Server Package Configuration, select the SSISConfig Connection Manager for the Connection; click the New button (cut off in screen shot below) next to Configuration table and accept the default to create a configuration table, and finally enter AdventureWorksConn as the Configuration filter:   13. Set the ConnectionString property of the AdventureWorks Connection Manager to the value of the SQL Server package configuration:   14. Set the Configuration name and click Finish:   15. Review the Package Configuration Organizer; you shocould see two deployments:   16. Open the configuration table in SQL Server Management Studio to view the configuration (see step 12 abve for the database name and table name ):   The schema of the SQL server package configuration table (as shown in Step 16 abve) includes des the following four columns: ConfigurationFilter-consider this as the unique key for the table; when you update the configuration table using a T-SQL UPDATE statement, this is the value you use in your WHERE clause ConfiguredValue-this column contains the configuration parameter value and is the one that you edit PackagePath-this column identifies a single property value in your SSIS package to be set to the value in the ConfiguredValue column ConfiguredValueType (cutoff in screen shot above)-the type of ConfiguredValue, usually String Caveats: When you add an Environment variable configuration (see step 9 above) and the environment variable does not appear in the Environment variable drop down list, close BIDS and reopen it. any environment variable added after opening BIDS will not show up until you close and reopen. When you are working with an SSIS package in BIDS, the package configuration is read when you open the package. any changes made to the configuration will not be reflected until you close and reopen the SSIS package. You can use an Environment variable package configuration to specify the ConnectionString property of the Configuration database Connection Manager, allowing you to change the server or database that holds the package configuration data. however, the table name that you specify (see step 12 abve) is hard-coded in the SSIS package. Next Steps Experiment with the SQL Server package configuration and see if it meets your configuration needs. Think of the SQL Server package configuration table as a group of parameter values for a participant application; you can use a different configuration table for each application, allowing you to specify different values as appropriate. remember that you specify the Configuration table name when you define the package configuration (see step 12 abve ). Stay tuned for an upcoming tip on defining reusable SSIS package templates; the SQL Server package configuration is an integral part of creating reusable package templates.