While deploying the packages to the 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 circumstances, we had SSIS Package Configuration settings. You can use them to save the connection manager details, variables, values, etc. Here, we show you the list of available SSIS Package Configurations and its description.
By default, the SSIS Package Configuration setting is not available in package designers. For instance, if you right-click on the control flow region, it will open the context menu. From the list, you can see there is no Package Configuration.. option.
To get that SSIS Package Configuration option, Please navigate yourself to Project Menu and select the Convert to Package Deployment Model sub-menu.
Once you select that menu, a new pop-up window will display, as shown below. Click the OK button.
After you click the OK button, SSIS will convert the current package into the deployment model. Click OK to close the conversion window.
Now, if you right-click on the SSIS control flow region, the context menu will display the Package Configurations.. option. You can select this option to add them.
Following is the list of package Configuration types available in SQL Server Integration Services or SSIS:
- XML Configuration File: This type will save the configuration settings in an XML file. It is the most common approach while deploying the report. Please refer to 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 to the Using Environment Variable article to understand the settings.
- Registry Entry: You can also save them in Registry. Visit using Registry Entry to know the registry settings.
- Parent Package Variable: This type will save them in a variable. You can use this variable to update properties in child packages. Visit the Parent-Child.
- SQL Server: Saved in a table present in it. Please see Using SQL Server to know the settings.
SSIS Sample Package for Configuration
We are going to use this package in the series of articles that explains each individual package configuration type.
Before we get into the SSIS Package Configuration demo, let me show you the table we will use. It is the Duplicate Employee Table.
As you can see from the below screenshot, it is a simple package that contains one Execute SQL Task and Script Task. Please refer Execute Task in the Single Rowset article to learn 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 connects to the database. Next, we use the Direct Input as the statement and write the custom command. Lastly, we changed the ResultSet from NONE (default) to Single row.
Let me click the … button to show the custom Query for 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 the first question mark beside the Yearly Income assigned to the Input Yearly Income variable, and the other question mark to the Input education variable.
As you can see from the screenshot below, we are assigning the previously created variables to all the columns returned by the statement.
I think I forgot to show the existing variables. Let me close the Execute 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 SSIS Package Configuration to see the result.
