In this article, we will show you how to apply the SSIS Package Configuration using SQL Server with example. We already explained the list of Package Configurations in the SSIS Package Configuration article.
SSIS Package Configuration using SQL Server Example
To add SQL SSIS package configuration, right-click on the control flow region will open the context menu. Please select the Package Configurations.. option from it
Once you choose the Package Configurations.., a new window called Package Configurations Organizer is opened. Please checkmark the Enable Package Configurations to allow the configurations. Next, click on the Add button to add a new SSIS Package Configuration using SQL Server.
Clicking the Add button will open a wizard. The first page is the welcome wizard. Checkmark Don’t shown this page again to avoid this page.
Here, we are explaining about SSIS package configuration using SQL Server. So, let me select the SQL Server as the configuration type.
- Connection: Specify the OLE DB Connection that is connecting to SQL Server.
- Configuration Table: Create, or select the configuration table that stores the configuration settings.
For now, we are selecting the existing connection. If you don’t have any connection, then click on the New button to create one.
Clicking the New button will open the Configure OLE DB Connection Manager. Please visit the OLE DB Connection Manager article
We haven’t created any configuration table. So, click on the New button
CREATE TABLE [dbo].[SSIS Configurations] ( ConfigurationFilter NVARCHAR(255) NOT NULL, ConfiguredValue NVARCHAR(255) NULL, PackagePath NVARCHAR(255) NOT NULL, ConfiguredValueType NVARCHAR(20) NOT NULL )
Please assign unique name to the Configuration filter.
Click Next button
Select Properties to Export: Specify the target value. Here we want to assign the package variables InputEducation, and InputYearlyIncome values. It means the default value of InputEducation (i.e., Masters Degree), InputYearlyIncome (i.e., 90000) will replace with the SSIS Configuration table values at run time.
Let me rename this SSIS Package Configuration using SQL Server name as Input Education Configuration. And click the Finish button to close the wizard.
Now you can see our newly created configuration that holds the variable values information inside the SQL table.
Let me open the SQL Server Management Studio, and check the details
Next, we used the UPDATE Statement to update the Configured Values as: InputYearlyIncome = 70000, and InputEducation = Bachelors
Let us run the package
From the above screenshot, see that it displayed the records whose Education is Bachelors, and yearly Income is 70000. It means, package variables values replaced by the SSIS Configuration table values