In this article we will show you, How to apply SSIS Package Configuration using SQL Server with example. We already explained the list of Package Configurations in our previous article. So, please refer SSIS Package Configuration to understand the same.
SSIS Package Configuration using SQL Server Example
Before we start SSIS package configuration using SQL Server, let me execute the package and see what is it returning.
In order to add SQL package configuration, right-click on the control flow region will open the context menu. Please select the Package Configurations.. option from the context menu
Once you select the Package Configurations.., a new window called Package Configurations Organizer is opened as we shown below. Please check mark the Enable Package Configurations to enable the configurations. Next, click on the Add button to add new configuration.
Clicking the Add button, will open a wizard. First page is the welcome wizard, please check mark Don’t shown this page again to avoid this page.
Select Configuration type: Here, we have to select the configuration type. By default XML configuration file is selected by the SSIS.
In this example 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. We already explained about the OLE DB Connection Manager so please refer the same.
We haven’t created any configuration table so, click on the New button
This will automatically generate Create Table Script for You. SQL Script that was generated is:
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: You have to specify the target value here. Here we want to assign the package variables InputEducation, and InputYearlyIncome values. It means, default value of InputEducation (i.e., Masters Degree), InputYearlyIncome (i.e., 90000) will be replaced with the SSIS Configuration table values at run time.
Let me rename the Configuration name as Input Education Configuration, and Click 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 you can see that, it is displaying the records whose Education is Bachelors, and yearly Income is 70000. It means, package variables values are replaced by the SSIS Configuration table values
