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.

SSIS Package Configuration using SQL Server 1

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

SSIS Package Configuration using SQL Server 2

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.

SSIS Package Configuration using SQL Server 3

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.

SSIS Package Configuration using SQL Server 4

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
SSIS Package Configuration using SQL Server 5

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.

SSIS Package Configuration using SQL Server 6

Clicking the New button will open the Configure OLE DB Connection Manager. Please visit the OLE DB Connection Manager article

SSIS Package Configuration using SQL Server 7

We haven’t created any configuration table. So, click on the New button

SSIS Package Configuration using SQL Server 8

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
)
SSIS Package Configuration using SQL Server 9

Please assign a unique name to the Configuration filter.

SSIS Package Configuration using SQL Server 10

Click Next button

SSIS Package Configuration using SQL Server 11

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.

SSIS Package Configuration using SQL Server 12

Let me rename this SSIS Package Configuration using SQL Server name as Input Education Configuration. And click the Finish button to close the wizard.

SSIS Package Configuration using SQL Server 13

Now you can see our newly created configuration that holds the variable values information inside the table.

SSIS Package Configuration using SQL Server 14

Let me open the Management Studio, and check the details

SSIS Package Configuration using SQL Server 15

Next, we used the UPDATE Statement to update the Configured Values as InputYearlyIncome = 70000, and InputEducation = Bachelors.

SSIS Package Configuration using SQL Server 16

Let us run the package

SSIS Package Configuration using SQL Server 17

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