SSIS Package Configuration using SQL Server

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 Package Configuration article.

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.

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.

Select Configuration type: Here, we have to select the configuration type. By default, SSIS selects the XML configuration file. Please refer to the below links for the remaining configurations.

1. Environment Variable
2. Parent-Child Package Configuration
3. Registry Entry
4. XML Configuration File
5. XML Configuration File in Environment Variable

Here, we are explaining about SSIS package configuration using SQL Server. So, let me select the Server as the configuration type.

• Connection: Specify the OLE DB Connection that is connecting to 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

It automatically generates a Create Table Script for you. The SQL Script generated by it 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 a 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 be replaced 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 table.

Let me open the 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