SSIS is completely replacement of DTS and it
becomes first class SQL Server components along with database engine, analysis
services, and reporting services. It is a good practice if you keep
configuration value not in SSIS but outside SSIS packages. Those configuration
values could be saved on XML configuration file, environment variable, windows
registry, parent package variable or SQL Server. The use of XML configuration
file is not uncommon now but it is less secure compared to save it into windows
registry or environment variable. But it is rigid implementation if the option
is to use windows registry or environment variable. The balance between security
and flexibility is to save it into SQL Server.
This posting will guide you how
to implement package configuration into SQL Server.
- Open your visual studio. Choose File menu > New > Project.
- In New Project dialog box, look at Project Types left pane, and choose item : Business Intelligence Projects.
- In Templates list box, choose Integration Services Project.
- In Name text box, fill out your SSIS project name and click OK button.
- Now you focus on Package.dtsx or if it is not opened yet, you double click on it at Solution Explorer window pane.
- Create package level variable, so go to SSIS menu > Variables.
- In Variables window pane, click New toolbar to create new SSIS variable. You can edit the variable name for example I name it myVar. Then change the variable data type to String. The variable value I would bind it with SSIS configuration with SQL Server type repository.
- Click blank area in the SSIS package or Control Flow designer window.
- Go to SSIS menu > Package Configurations…
- In Package Configuration Organizer window, tick check box “Enable package configurations”
- Click Add button
- In Package Configuration Wizard, click Next button
- You will be presented with Select Configuration Type section, choose SQL Server in Configuration Type combo box.
- Make sure you choose “Specify Configuration settings directly”, click New button beside Connection combo box.
- In Configure OLE DB Connection Manager window, choose New button.
- Type your database server name, database name, and click “Test Connection” button to test whether you can connect to database server. If test is successful, click OK button to close Connection Manager window and go back to Configure OLE DB Connection Manager. And click OK to go back to Package Configuration Wizard window.
- Click New button beside Configuration Table combo box. And click OK button. The wizard will create SSIS configuration table.
- Type your Configuration Filter, for example Configuration.VariableA and click Next button.
- In “Select Properties to Export” section, from Objects list box, go to the variable that is created on step 7, expand its properties and tick Value property.
- Click Next button to go to “Completing the Wizard” section, name your configuration name, for example MyConfiguration and click Finish button to go back to Package Configurations Organizer window.
- click Close button.
- by default myVar variable value is still empty, you need to fill it from “SSIS Configuration” table, so open SSMS (SQL Server Management Studio) and open the table.
- After you open “SSIS Configuration” table, go to Configuration.VariableA and type any value in ConfiguredValue column then commit the change.
- go back to BIDS, still you find the SSIS variable value is empty, you need to refresh it by close the package designer window and reopen it.
- Now you will find the variable value is filled with value from SQL Server configuration repository.
