SSIS Package Configuration

While deploying the packages to production environment, it is a bad practice to edit the package configuration settings in BIDS. If you place connection manager details inside the package then you have to edit them using BIDS only. To avoid these situations we had SSIS Package Configuration settings. You can use this to save the connection manager details, variables values etc. In this article we will show you the list of available SSIS Package Configurations, and its description.

By default SSIS Package Configuration setting are not available in package designer. For instance, if you right-click on the control flow region, it will open the context menu. From the menu, you can see there is no Package Configuration.. option.

In order to get that option, Please navigate yourself to Project Menu, and select Convert to package Deployment Model sub menu as we shown below.

Once you select that menu, a new pop-up window will be displayed as we shown below. Click OK button

After you click the OK button, SSIS will convert the current package in to deployment model. Click OK to close the conversion window.

Now, if you right-click on the control flow region, context menu will display the Package Configurations.. option. You can select this option to add the package configurations.

Following are the list of Configuration types available in SQL Server Integration Services:

• XML Configuration File: This type will save the configuration settings in XML file. This is the most common approach while deploying report. Please refer SSIS Package Configuration using XML Configuration File, and Part 2 to understand the XML Configurations.
• Environment Variable: It allows you to use the Environment variables to save the configuration settings, or package variables values. Please refer SSIS Package Configuration Using Environment Variable article to understand the settings.
• Registry Entry: You can also save the configuration settings in Registry. Please refer SSIS Package Configuration using Registry Entry article to understand the registry settings.
• Parent Package Variable: This type will save the configuration settings in a variable. You can use this variable to update properties in child packages.
• SQL Server: Configuration settings will be saved in a table present in the SQL Server. Please refer SSIS Package Configuration using SQL Server article to understand the SQL settings.

Sample package for SSIS Package Configuration

This is the example package that we are going to use in the series of articles that will explain you each and individual package configuration type in SSIS. Before we get into the package, let me show you the table that we are going to use. This is the Duplicate Employee Table in SQL Server that we are going to use in this example:

As you can see from the below screenshot, it is a simple package that contains one Execute SQL Task, and Script Task. We already explained this package in our previous article. So, please refer Execute SQL Task in SSIS Single Rowset article to under the package configurations.

Let me show you the configuration settings inside the Execute SQL Task. Here, we selected the Connection Type as OLE DB Connection, which is connecting to SQL Tutorial database. Next, we are using the Direct Input as the SQL statement, and written the custom SQL command. Lastly, we changed the Result Set from NONE (default) to Single row.

Let me click the … button to shown the custom SQL Query.

-- SSIS Package Configuration Example
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[Sales]
      ,[HireDate]
      ,[YearlyIncome]
  FROM [DuplicateEmployee]
  WHERE [YearlyIncome] = ? AND
        [Education] = ?

Under the Parameter Mapping tab, we are assigning the variables to the parameters. It means, first question marks beside the Yearly Income is assigned to Input Yearly Income variable, and the other question mark to Input education variable.

As you can see from the below screenshot, we are assigning the previously created variables to all the columns that are returned by the SQL statement.

I think I forgot to show the existing variables. Let me close the Execute SQL Task editor, and right-click on the design to select the variables.. option. From the below screenshot you can see the default values of InputEducation= Masters Degree, and InputYearlyIncome = 90000

C# code inside the Script Task is as shown below.

Let us Run the package to see the result.

Thank You for Visiting Our Blog