Package Configuration Part II - SSIS

This section covers older configurations. You will need this information if you decide to run your packages with the package model and use configuration files or tables, rather than upgrade your packages to use parameters and environments in the project deployment model.

Now that you have a set of packages complete, the challenge is trying to deploy those packages to your testing environment or production without having to manually configure the packages for that environment. For example, your production server may not have the same directory to pull extract files from or the same user name for connecting to the database. Configuration files and tables help you make the migrations seamless and automated to reduce your risk of errors. In this section, you’ll see how to create a configuration repository for files and how to set up a configuration table.

The SSIS Package Configuration option allows you to write any SSIS property for the package, connection, container, variable, or any task into an XML file or a table, for example, and then read the setting at runtime. You could deploy the configuration file to multiple servers and point the setting inside the file to a new SQL Server database on the second server, and when the package runs, it will automatically shift its connection to the new database. Configurations also come in handy later when you deploy the packages to production using the deployment utility.

The following short example demonstrates the strengths and weaknesses of package configurations. In this example, you’re going to create a simple package with a Script Task that will pop up a message with the configuration value instead of its normal, hardcoded value. You’ll then create multiple configuration files and see which configuration file is used by the package.

First, create a new package called ConfigFiles.dtsx in a project set to package deployment model. Drag a new Script Task onto the Control Flow tab in the newly created package and name the task Popup Value. Next, create a new string variable called strMessage that is scoped to the package, not the Script Task. Seed a default value of “Hard-Coded Value” for the string variable.

Double-click the Script Task to configure it. In the Script page, type strMessage for the ReadOnlyVariables property. Change the ScriptLanguage property to Microsoft Visual Basic 2012. Click Edit Script to add your code to the task. Double-click the ScriptMain.vb file in the Project Explorer window if it isn’t already open. The code you’re going to add will pop up the value from the strMessage variable by using the following code in the Main() subroutine:

Public Sub Main()
”
Add your code here
MsgBox(Dts.Variables(“strMessage”).Value)
Dts.TaskResult = ScriptResults.Success
End Sub

Close the task. If you execute the package at this point, you should see a popup dialog that states “Hard-Coded Value.” If so, you’re now ready to set this variable from a configuration file instead.

Select Package Configurations either from the SSIS menu or by right-clicking in the background of the Control Flow tab. This opens the Package Configurations Organizer, where you will create and arrange the priority of your package configurations. Click Enable Package Configurations to enable this feature.

In this screen, you can check any property that you wish to have read from the configuration file. In this case, you want to drill down to Variables ⇒ strMessage ⇒ Properties and check the Value option (as shown in below screenshot). Click Next to proceed to the next screen.

You are then taken to the summary screen, where you should name the configuration strMessage and click Finish, which takes you back to the Package Configurations Organizer. Click Close to exit the organizer and execute the package. If you run the package again, you’ll see that the pop-up still has the same old message. The configuration file now has been created and contains the same value as the package.

Open the configuration.xml file in your favourite XML editor or Notepad, and replace the old variable value of “Hard-Coded Value” with a new value of “Config File Changed Value,” as shown in the following code. The other pieces of the configuration file contain a lot of metadata about who created the configuration file and from what package.

When you execute the package again, notice this time the message has changed to “Config File Changed Value.”

You can also create multiple configuration files. For example, you may want a configuration file that contains your corporate logging database for all of your packages to use, and then another configuration file for the individual package. As you add more package configurations, they stack on top of each other in the Configurations Organizer screen. At runtime, if there is a conflict between two configurations, the last configuration on the bottom will win.

To demonstrate this, create one additional configuration. This time, when you’re asked for the configuration type, select SQL Server. For the Connection property, select New and point the connection to the AdventureWorks database, which will create a Connection Manager. Lastly, click New for the Configuration Table property. The table can be called whatever you’d like as long as you have the core four columns. Name the table ctrlConfigurations, as shown in the following script:

CREATE TABLE [dbo].[ctrlConfigurations]
(
ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL
)

Frequently Asked SSIS Interview Questions & Answers

 

Type Development for the Configuration Filter. When the package reads from the ctrlConfigurations table, it will read all the properties where the ConfigurationFilter column is equal to “Development,” as shown in below screenshot. Typically, you would want to set this filter to either the package name or the group of packages that you wish to share the same configuration settings. This is because all configurations in SQL Server are stored in the same table.

In the next screen, select the value property of the strMessage variable. Click Next to go to the next screen and name this configuration “SQL Server Config.” You should now have two package configurations, as shown in below screenshot. Set the variable’s value by going to the ctrlConfigurations table in the AdventureWorks database and setting the ConfiguredValue column to “SQL Server Config Value,” as shown in the following query:

Update ctrlConfigurations
SET ConfiguredValue = ‘SQL Server Config Value’
where ConfiguredValue = ‘Hard Coded Value’

When you execute the package, notice that the value that pops up now is “SQL Server Config Value.” This is because even though there were two configurations that set the same variable, the one at the bottom (see below screenshot) set the value last. Package configurations make it easy to migrate a package from environment to environment. For the most part, it’s going to be easier to store your configurations in the SQL Server because you can write some sort of frontend to modify the settings, and you can create reports to view the settings. The main problem with package configurations is that data is not encrypted, so you should not store anything that must be secure inside package configurations.

There are a few methodologies you can employ when you use configuration files. One is to group all the like configuration properties together into files or with filters if you choose to store the settings in a table. The other option, which many prefer, is to store each property in its own file or with its own filter. The latter option requires more work in terms of creating your package because you may have to create dozens of files, but it enables you to pick which settings you like and reuse them repeatedly.

In this section you learned how to use configuration files and configuration tables to make deployment easier in the older package deployment model. However, at the risk of my sounding like a broken record, I’ll say that you should be updating your methods to take full advantage of the SSIS catalog features.

 

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator