SQL Server Integration Services Design Patterns, Second Edition (2014)

Chapter 17. Configuration

SQL Server 2012 introduced a new, parameter-based configuration model for SSIS. This new model is meant to simplify the configuration process and make it easier for users to identify where values are coming from at runtime. Although 2005/2008-style package configurations are still supported in SQL Server 2012 and SQL Server 2014, the two configuration models are not meant to be mixed. In fact, the menu option to use them will only appear when you are using the file deployment model and on packages that have been upgraded from previous versions. New packages created in SQL Server 2014 will use the new parameter model by default.

This chapter describes the new parameter model and how it can be used to configure package properties at runtime. We’ll look at how parameters are exposed in the SSIS Catalog and how you can set parameter values as part of your build process using Visual Studio configurations. Finally, we’ll look at design patterns that you can use to augment the functionality provided by the built-in parameter model, providing dynamic runtime configuration.

Parameters

SSIS parameters allow packages to define an explicit contract, much like function parameters do in programming languages like C#. Unlike package configurations, parameters are exposed to the callers, like SQL Server Agent, or the Execute Package task, so users are able to see exactly what a package needs to run. Parameters are essentially read-only package variables in a special namespace. They follow the same type system as package variables and will appear in all of the same UIs that variables do (for example, for setting property expressions). You’ll make use of parameter values through expressions or by reading them in a Script task. Parameter values are set before package execution begins, and their value cannot be changed while the package is running.

Parameters can be defined at the package level and at the project level. Package-level parameters are visible only to tasks and components within that package—much like package variables. Package parameters are defined in the $Package namespace. Parameters defined at the project level are global—all packages within the project are able to make use of them. Project parameters are defined in the $Project namespace.

Figure 17-1 shows the new Parameters tab in SQL Server Data Tools for Business Inteligence (SSDT-BI), which displays parameters defined at the package level. In addition to the standard properties you’d find on a package variable (such as Name, Type, and Value), the Parameters tab expose three new properties: Description, Sensitive, and Required.

Figure 17-1. Package-level parameters are created and displayed on their own tab in SSDT-BI

The Description field provides an easy way for the SSIS package developers to document the arguments for their packages. It’s recommended that you provide descriptions for your parameters, especially in cases where the person running or configuring the packages is not the same person who developed them.

If a parameter is marked as Sensitive, its value will be stored in an encrypted format within the package (or it will not be stored at all, depending on the package’s ProtectionLevel setting). Its value will also be masked when it is displayed in the UI and will not be displayed in execution logs. Sensitive parameters can only be used in expressions for properties that are marked as Sensitive (such as the Password property of a connection manager). Sensitive parameter values can also be retrieved in a Script task or script component with theVariable.GetSensitiveValue() method.

Parameters that are marked as Required must have their values specified at runtime. All parameters (and variables) need values set at design time for validation purposes. Required parameters will not use this design time value when the package runs—a new value must be specified by the caller (i.e., SQL Server Agent or the parent Execute Package task). If a parameter’s Required property is set to False, the parameter becomes optional—its design time value will be used if no other value is supplied. Parameters that have no logical default value (such as a BatchIDor path to an input file) should be marked as Required.

Project-level parameters can be found by accessing the new node in the Solution Explorer (as shown in Figure 17-2). Project parameters appear in their own node because they are stored in a separate file (Project.params) within the solution directory. Double-clicking this node brings up the same parameter designer used for package parameters with all of the same properties and options.

Figure 17-2. Project-level parameters can be found in the Project.params node in Solution Explorer

Configuring Your Package Using Parameters

Parameter values are used in your package via SSIS Expressions. Expressions can be set on most task properties, variables, and certain component properties in a Data Flow task. To set an expression on a task, open the Property Expressions Editor dialog (shown in Figure 17-3) by clicking on the expression’s property in a task’s Properties window.

Figure 17-3. The Property Expressions Editor dialog shows all properties that have expressions set on them

Expressions can be set on variables directly from the Variables window (as shown in Figure 17-4). In SQL Server 2012, adding an expression to a variable automatically sets its EvaluateAsExpression property to True—in previous versions of the product, you had to perform this step yourself. You can disable expression evaluation for a variable by setting this property back to False.

Figure 17-4. Expressions can be set directly from the Variables window in SQL Server 2012. Variables that have an expression set on them appear with a special icon

Figure 17-4 also shows a new feature in SQL Server 2012—expression adorners. The icons for tasks, connection managers, and variables will change if any of the object’s properties are set via expression, providing a visual way for a developer to identify which parts of a package are being set dynamically.

Setting expressions for data flow components is less straightforward than setting them on tasks. The main differences are that the expressions are set on the Data Flow task itself and that not all component properties are expressionable. Figure 17-5 shows how expressionable properties on a Lookup transform “bubble up” and appear as properties on the Data Flow task.

Figure 17-5. Expressionable data flow component properties will show up as properties on the Data Flow task

Package and project-level parameters will appear in all of the UIs that display the list of available variables. On the Expression Builder dialog (Figure 17-6), all parameters appear under the Variables and Parameters folder.

Figure 17-6. Parameters appear alongside variables in the Expression Builder dialog

Certain tasks and data flow components are able to make use of variable and parameter values without the use of expressions. For example, the OLE DB Source provides a “SQL command from variable” data access mode that allows you to set the source query from a variable. Parameters can be used instead of variables for all such properties.

Using the Parametrize Dialog

SSIS provides a Parameterize UI (shown in Figure 17-7) that acts as a shortcut for making use of parameters in your packages. From this UI, you can create a new parameter or use one that already exists. To launch the Parameterize UI, right-click on the task, container, or control flow, and select Parameterize from the context menu. When you click OK, SSIS will automatically add an expression to the selected property.

Figure 17-7. The Parameterize UI is a shortcut for making use of parameters in your package

Creating Visual Studio Configurations

You can use Visual Studio configurations to create multiple sets of parameter values within SSDT-BI. Switching between configurations allows you to easily change parameter values during development and also allows you to build multiple versions of your project deployment file with different default parameter values. Visual Studio configurations are a way for developers to maintain their own settings in multideveloper or team environments.

When you first create a project within SSDT-BI, you will have a default configuration called Development. You can create additional configurations from the Configuration Manager dialog (shown in Figure 17-8). You can launch the Configuration Manager dialog from the Solution Configurations combo box on the Standard toolbar or by right-clicking on the project node in the Solution Explorer, selecting Properties, and clicking the Configuration Managers button. To create a new configuration, select the <New...>option from the Active Solution Configuration dropdown.

Figure 17-8. Visual Studio configurations can be managed from the Configuration Manager dialog

To add a parameter to a configuration, click the Add Parameters to Configurations button on the package Parameters tab. Figure 17-9 shows the Manage Parameter Values dialog that will be displayed when you add package parameters to configurations. Clicking the Add button allows you to select a parameter–once a parameter is added, it will appear in all configurations in the solution. The Remove button will remove the selected parameter from configurations (which means it will always have the same default value at design time). The Sync button will apply the same value to all configurations—use this button as a shortcut when you’re sure that the parameter’s default value should change across all configurations. Currently you can only add package parameters and project parameters to Visual Studio configurations, but they are configured from separate dialogs. To manage project-level parameters, click the Add Parameters to Configurations button from the Project Parameters designer (Project.params). To manage a connection manager’s settings with Visual Studio configurations, you will first need to parameterize the Connection Manager. Shared connection managers cannot be configured using Visual Studio configurations.

Figure 17-9. The Manage Parameter Values dialog displays all parameters currently set via configurations

Note When a parameter is controlled by Visual Studio configurations, its value is saved out to the Visual Studio project file (.dtproj). Be sure to save the project file after making updates to your configurations to make sure that you do not lose the changes.

Specifying Entry-Point Packages

SQL Server 2012 introduces another new concept for SSIS—the entry-point package. This feature allows the package developer to indicate that special attention should be paid to certain packages. This is very useful in projects that contain a small number of master packages that run a number of child packages. Note that packages that are not marked as entry-point packages can still be run—the setting is meant to be a hint for the person configuring parameter values in the SSIS Catalog. Most SSIS UIs in SQL Server Management Studio (SSMS) allow you to quickly filter out parameters on non-entry-point packages, allowing you to view only the parameters they need to set.

Packages are marked as entry points by default. To remove this setting, right-click on the package name in the Solution Explorer, and unselect the Entry-Point Package option.

Connection Managers

Most connection managers will require some form of configuration, and in SQL Server 2012, all connection manager properties are configurable when packages are run through the SSIS Catalog. Since these properties are already exposed, in most cases, you will not need to expose additional parameters for your connection managers. However, you may encounter some scenarios where parameterized connection managers will be beneficial. Note that any connection manager property that is set via expression will not be exposed through the SSIS Catalog, which prevents a DBA from accidentally overriding property values that are set at runtime.

Note In previous versions of SQL Server Integration Services, it was common for child packages to configure connection managers with variable values from the parent package. You may wish to keep this pattern in SQL Server 2014 if the connection string is determined at runtime; however, in many cases, you’ll want to use shared connection managers instead.

Parameters can be set on connection managers using property expressions. The most common property to set via expression is the ConnectionString, because many connection managers derive their properties by parsing the ConnectionString value. When configuring connection managers, be sure to set expressions on either the ConnectionString or individual properties—the order in which expressions are resolved cannot be guaranteed, and certain properties may be overwritten when the ConnectionString is applied.

To parameterize a shared connection manager, open one of the packages in the project and right-click on the shared connection manager’s name in the Connection Managers area of the design surface. Note that since shared connection managers are declared at the project level, you can only use project-level parameters or static strings in any property expressions on shared connection managers. The expression dialog will not give you the option to use package parameters or variables.

Parameter Configuration on the Server

Parameters were designed to make it easier for the person scheduling and running SSIS packages. In many environments, this is typically a DBA or IT operations person—not the person who originally developed the package. By including descriptions with the parameters, an ETL developer can create self-documenting packages, making it very easy for whoever is configuring the package to see exactly what it needs to run.

This section describes how to configure packages through the SSIS Catalog and how to surface parameters through SSMS. It covers how to set default parameter values after a project is deployed, the various package execution options, and how the built-in reporting functionality in SQL Server 2012 makes it easier to determine the exact configuration values set when the package was run.

Default Configuration

Default values for all parameters and connection managers are saved within the SSIS project deployment file (.ispac) when the file is built. These become the default values for the project once it is deployed to the SSIS Catalog. To change the default configuration, right-click on the project name (or individual package names) and select Configure within the SSMS Object Explorer (as shown in Figure 17-10).

Figure 17-10. The default configuration for a project can be changed through SSMS after the project is deployed

Figure 17-11 shows the parameter configuration dialog in SSMS. Through this dialog, you can set default values for all parameters and connection manager properties for packages within this project. The Scope dropdown allows you to filter your view of the parameters and connection managers. The default view will display entry-point packages only, but you can also view parameters for individual packages and for the entire project. To change the value for a parameter or connection manager property, click the ellipses button at the end of the row. You will have three options when you go to change a value: use the project default, set a literal value, or use a server environment variable. For more information about environments, see the next section.

Figure 17-11. Parameter configuration dialog

Server Environments

Server environments contain a set of variables—essentially name-value pairs—that you can map to parameters and connection manager properties within your project. When you run a package through the SSIS Catalog, you can select an environment to run it in. When a value is mapped to a server environment variable, its value will be determined by the environment it is currently running in.

Before you can map a value to a server environment variable, you must associate the environment with the project. Figure 17-12 shows the References page of the project Configure dialog, which allows you to associate a project with one or more environments.

Figure 17-12. The References page of the Configure dialog lets you associate a project with environments

Like projects, environments are contained within a folder in the SSIS Catalog. A project may reference an environment in any folder in the catalog—references are not limited to the current folder only. If you plan to use environments throughout your projects, you might consider creating a separate folder as an area to store all of the common environments.

Environments support row-level security. Like projects and folders, you can configure which users or roles have access to individual environments. Users will not be able to see environments they do not have access to.

Once a project has been associated with one or more server environments, you are able to map parameter and connection manager values to variables contained within those environments.

Note Environments can contain any number of server variables, and two environments might not contain variables with the same name. If a parameter or connection manager value is mapped to a server variable, only environments that contain a variable with that name (and matching data type!) will be available when you go to select the environment to run the package in.

Default Parameter Values Using T-SQL

Default parameter values and connection manager properties can be set through the SSIS Catalog’s T-SQL API. This allows a DBA to automate the setting of parameter values after a deployment or after a project is moved to a new SSIS Catalog. An easy way to create a script is to make the changes through the parameter configuration UI, and then click the Script button. Listing 17-1 shows the T-SQL used to set default values for a two items: a package parameter (MaxCount) is set to 100, and a connection manager property (CM.SourceFile.ConnectionString) is set to 'C:\Demos\Data\RaggedRight.txt'.

Listing 17-1. Setting Parameter Values Using T-SQL

DECLARE @var sql_variant = N'C:\Demos\Data\RaggedRight.txt'
EXEC [SSISDB].[catalog].[set_object_parameter_value]
@object_type=20,
@parameter_name=N'CM.SourceFile.ConnectionString',
@object_name=N'ExecutionDemo',
@folder_name=N'ETL',
@project_name=N'ExecutionDemo',
@value_type=V,
@parameter_value=@var
GO

DECLARE @var bigint = 100
EXEC [SSISDB].[catalog].[set_object_parameter_value]
@object_type=30,
@parameter_name=N'MaxCount',
@object_name=N'LongRunning.dtsx',
@folder_name=N'ETL',
@project_name=N'ExecutionDemo',
@value_type=V,
@parameter_value=@var
GO

Note For more information, see the set_object_parameter_value stored procedure entry in Books Online: http://msdn.microsoft.com/en-us/library/ff878162(sql.110).aspx.

Package Execution Through the SSIS Catalog

Default values for parameters and connection manager properties can be overridden when a package is executed. The Execute Package UI in SSMS (shown in Figure 17-13) allows you to specify the values to use for that specific execution of the package. Project and package-level parameters are displayed on the Parameters tab and shared connection managers and package-level connection managers are shown in the Connection Managers tab. The Advanced tab allows you to override property values that were not exposed as parameters. This feature—called Property Overrides—allows a DBA to make a quick configuration change to a value within a package without having to redeploy the entire project. The functionality is similar to using the /Set command line option with the DTEXEC utility.

Figure 17-13. Interactive package execution through SSMS

The Execute Package UI also has a Script menu which allows you to script out the creation of a package execution to T-SQL. Listing 17-2 provides an example of creating a new package execution and overriding a number of settings. This procedure involves a number of steps:

1. Create a new execution instance using [catalog].[create_execution].

2. Override parameter or connection manager values using [catalog].[set_execution_parameter_value].

3. Set property overrides using [catalog].[set_execution_property_override_value].

4. Start the package execution using [catalog].[start_execution].

Listing 17-2. Running a Package Using T-SQL

-- Create the package execution
DECLARE @exec_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@execution_id=@exec_id OUTPUT,
@package_name=N'LoadCustomers.dtsx',
@folder_name=N'ETL',
@project_name=N'ExecutionDemo',
@use32bitruntime=0

-- Set a new value for the AlwaysCheckForRowDelimiters property of the
-- SourceFile connection manager
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id=@exec_id,
@object_type=20,
@parameter_name=N'CM.SourceFile.AlwaysCheckForRowDelimiters',
@parameter_value=0

-- Set the logging level for this execution
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id=@exec_id,
@object_type=50,
@parameter_name=N'LOGGING_LEVEL',
@parameter_value=1

-- Create a property override for the MaxConcurrentExecutables property
EXEC [SSISDB].[catalog].[set_execution_property_override_value]
@execution_id=@exec_id,
@property_path=N'\Package.Properties[MaxConcurrentExecutables]',
@property_value=N'1',
@sensitive=0

-- Start the package execution
EXEC [SSISDB].[catalog].[start_execution] @exec_id

-- Return the execution ID
SELECT @exec_id

GO

The Integration Services job steps in SQL Server Agent have been enhanced in SQL Server 2012 to support running packages stored in an SSIS Catalog. The user interface is the same as when you run a package interactively through SSMS, and it provides the same configuration options. Alternatively, you can run SSIS packages using the T-SQL job step. However, since this step does not support the use of proxy accounts, you will be limited to running the packages as the SQL Server Agent service account.

Parameters with DTEXEC

The command prompt utility to run SSIS packages (DTEXEC) has been updated to support projects and parameters. DTEXEC is able to run packages stored within an SSIS project file (.ispac) as well as start a server-based execution of a package stored within an SSIS Catalog (local or remote). Both modes use different command-line switches to set parameter values and are described in separate sections in the following pages.

Note When working with individual SSIS package files (.dtsx), DTEXEC behaves the same as it did in previous versions of SQL Server. For more information on the various command-line options for DTEXEC, see its entry in Books Online: http://msdn.microsoft.com/en-us/library/ms162810.aspx.

Projects on the File System

Although the new project deployment model is primarily meant to be used with the SSIS Catalog, it is possible to run packages within a project file using DTEXEC. Packages run this way are executed locally by the DTEXEC process. Individual parameter values can be set using the /Setoption, and /ConfigFile can be used to set a number of parameter values from a 2005/2008-style XML configuration file. Table 17-1 provides a summary of the options related to running packages from projects stored on the file system.

Table 17-1. DTEXEC Command-Line Options for Using Project Files (.ispac)

Listing 17-3 provides an example of running a package (MyPackage.dtsx) contained within a project file (project.ispac). It sets the values for two parameters—BatchNumber, an integer parameter defined at the project level, and HostName, a string parameter defined at the package level.

Listing 17-3. Running Packages Within a Project File Using DTEXEC

dtexec.exe /Project c:\demo\project.ispac /Package MyPackage.dtsx /Set
\Package.Variables[$Project::BatchNumber];432 /Set
\Package.Variables[$Package::HostName];localhost

Note Although the syntax for setting parameter values is similar to setting values for variables and other package properties, there is one key difference. To set parameter values, you should not include the name of the property—you only specify the name of the parameter itself.

Projects in the SSIS Catalog

DTEXEC has been extended in SQL Server 2012 to support running packages contained within an SSIS Catalog. Unlike other execution modes, when running a package from a catalog, the execution takes place on the SSIS Catalog’s server and not by the DTEXEC process. In this mode, you will use the /ISServer command-line option to specify the path to the package you want to run, the /Parameter option to set parameter values, and the /EnvReference option if you wish to run your package in a specific server environment. Table 17-2 contains a full list of command-line options for SSIS Catalog-based execution with DTEXEC.

Table 17-2. DTEXEC Command-Line Options for the SSIS Catalog

Listing 17-4 provides an example of running a package (MyPackage.dtsx) contained within a project (ETLProject) in a folder (MyFolder) on a remote SSIS Catalog server (ETLServer). It sets the values for two parameters—BatchNumber, an integer parameter defined at the project level, and HostName, a string parameter defined at the package level. It also sets the SYNCHRONIZED server option to True, which tells DTEXEC to run in a synchronous mode—more details on synchronous vs. asynchronous execution will come in the following pages.

Listing 17-4. Running Packages Within an SSIS Catalog Using DTEXEC

C:\>dtexec.exe /Ser ETLServer /IS \SSISDB\MyFolder\ETLProject\MyPackage.dtsx /Par
$Project::BatchNumber;432 /Par $Package::HostName;localhost /Par
"$ServerOption::SYNCHRONIZED(Boolean)";True

Microsoft (R) SQL Server Execute Package Utility
Version 12.0.2000.8 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started: 4:46:44 PM
Execution ID: 4.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started: 4:46:44 PM
Finished: 4:49:45 PM
Elapsed: 3 seconds

Note You must use Windows Authentication to connect to your SQL Server instance when you are running packages contained in an SSIS Catalog. The /User and /Password command-line options cannot be used with the /ISServer option. If you need to impersonate another user account, you can use the RunAs DOS command with DTEXEC.

When you run an SSIS Catalog package with DTEXEC, it will run in an asynchronous mode by default. This means that the process will return immediately and will not tell you whether the package actually ran successfully. To get synchronous execution behavior (e.g., the same that you would get when running packages from the file system or MSDB), you need to include the /Par "$ServerOption::SYNCHRONIZED(Boolean)";True command-line switch. When synchronous execution is used, the DTEXEC process will not return until the package has finished running.

Another difference between the SSIS Catalog and other forms of DTEXEC execution is that the events that occur while the package is running are not displayed on the command line. Listing 17-4 shows a sample output from running a package in the SSIS Catalog—as you can see, there is only a single message telling you the server execution ID and pointing you to the catalog reports.

Dynamic Configurations

Parameters on an entry-point package allow a user to specify values, but they require that the values be known before the package starts running. There may be times where you’ll need to determine configurations at runtime or dynamically pull in values from other sources (such as an external file or a database table). The following sections provide design patterns that you can use to augment the capabilities provided by the parameter model.

Configuring from a Database Table

The SSIS Catalog provides a central location for package configuration values, but your environment may already have alternative locations that store metadata that your packages need at runtime. This pattern shows you how to retrieve values from a database table using an Execute SQL task and how to configure properties within the package using property expressions. For this example, you’ll be reading a directory and file name from a database, storing the values in variables, and then using them to dynamically set the ConnectionString for a flat file connection manager.

Creating the Database Table

Listing 17-5 shows the SQL for the table that you will be reading your configuration values from. Each row in the table is a new flat file that you will want to process with this package. The two main columns you are interested in are directory and name—the id column is a surrogate key to uniquely identify each row in the table, and the processed column lets us easily filter out files that have already been processed. Sample values are shown in Table 17-3.

Listing 17-5. SQL Definition of the Table Our Package Will Read Its Configuration Values From

CREATE TABLE [dbo].[PackageConfiguration]
(
[id] int IDENTITY(1,1) NOT NULL,
[directory] nvarchar(255) NOT NULL,
[name] nvarchar(255) NOT NULL,
[processed] bit NOT NULL
)

Table 17-3. Sample Rows from the PackageConfiguration Table

Retrieving Configuration Values with an Execute SQL Task

You will retrieve the list of files you need to process from the PackageConfiguration table you created using an Execute SQL task. You will store the result set in a package variable, and then loop through each row with a Foreach loop container. You will use the processed field to mark the files that have already been processed—you will set the processed value to True once you have successfully loaded the file.

Note This example assumes that all of the flat files listed in the PackageConfiguration table have the same schema. It does not cover the logic needed to actually load the flat file into the database—it is meant to illustrate the pattern that you’d use as a template for processing a number of items in a loop.

Setting up the package takes the following steps:

1. Add four package variables.

· FileID (Int32): The row ID for the file you are currently processing

· Directory (String): The directory containing the flat file you need to process

· FileName (String): The name of the file you are processing

· FilesToProcess (Object): The result set of the Execute SQL task

2. Add an Execute SQL task to your package; name it Retrieve File List.

3. Double-click the task to open its editor.

4. Ensure the ConnectionType is OLE DB.

5. Click on the Connection dropdown and select New connection....

6. Click New and configure the connection manager to point to the database containing the PackageConfiguration table.

7. Select all of the files that have not been processed from the PackageConfiguration table (as shown in Listing 17-6).

Listing 17-6. Query to Pull Out All Entries in the Configuration Table That Have NotBeen Processed Yet

SELECT * FROM [dbo].[PackageConfiguration] WHERE [processed] = 0

8. Set the ResultSet value to Full Result Set. This means that the Execute SQL task will retrieve the values as an ADO Recordset that can be processed by the Foreach loop. Note that you could also use an ADO.NET connection manager here, which would cause the results to be returned as an ADO.NET data table.

9. Click on the Result Set tab.

10.Click Add, and use these mappings:

a. Result Name:0

b. Variable Name:User::FilesToProcess

11.Click OK to save the changes to the Execute SQL task.

12.Add a Foreach loop container to your package.

13.Connect the Execute SQL task to the Foreach loop container.

14.Add a Data Flow task inside of the Foreach loop container.

15.Add a new Execute SQL task inside of the Foreach loop container.

16.Connect the Data Flow task to the Execute SQL task.

17.Double-click the Execute SQL task to open its editor.

18.Set the connection to the same connection manager you created in step 5.

19.Listing 17-7 shows the SQLStatement to mark a row in the table as processed. Note that the statement contains a parameter marker (the question mark). You will map a variable value to this parameter in the next step.

Listing 17-7. SQL Statement to Mark the File as Processed

UPDATE [dbo].[PackageConfiguration] SET [processed] = 1 WHERE id = ?

20.Click the Parameter Mapping tab.

21.Click Add, and use these mappings:

. Variable name:User::FileID

a. Data Type:LONG

b. Parameter Name:0

22.Click OK to save the changes to the Execute SQL task.

23.Add a new Flat File connection manager, and point it to an existing flat file.

24.Right-click on the Flat File connection manager, and select Properties.

25.Select the Expression property, and bring up the Property Expression editor.

26.Set an expression on the ConnectionString property that makes use of the variable values retrieved from the PackageConfiguration table. Listing 17-8 provides an example of the expression.

Listing 17-8. Expression to Set the Path to the Input File on the ConnectionString Property

@[User::Directory] + "\\" + @[User::FileName]

Your package should now look like Figure 17-14.

Figure 17-14. Package configured for Execute SQL task–based dynamic configurations

Setting Values Using a Script Task

An alternative to retrieving your configuration with an Execute SQL task and setting package properties through expressions is to use a Script task. This approach can be useful if your values aren’t coming from a database or they require additional processing logic—for example, if they are coming from an encrypted source. From within a Script task, you can easily read values from external configuration files (such as an XML file) and access shared configuration resources that might be used by other, non-SSIS parts of your data integration solution. The Script task is able to read and modify package properties at runtime, including the variable values and all connection manager properties.

Listing 17-9 provides sample code of a Script task that sets a connection manager’s ConnectionString at runtime.

Listing 17-9. Sample Code to Set Package Properties Using a Script Task

public void Main()
{
// TODO: This would be set from an external configuration file
const string SourceSystemConnectionString = "...";

Dts.TaskResult = (int)ScriptResults.Success;

if (Dts.Connections.Contains("SourceSystem"))
{
ConnectionManager cm = Dts.Connections["SourceSystem"];
cm.ConnectionString = SourceSystemConnectionString;
}
else
{
// The expected connection manager wasn't found - log and set an error status
Dts.Events.FireError(0, "Script Task",
"Could not find the SourceSystem connection manager",
string.Empty, 0);

Dts.TaskResult = (int)ScriptResults.Failure;
}
}

Dynamic Package Executions

In this approach, you will use the same table from Listing 17-5, but instead of reading the configuration values with an SSIS package, you’ll use T-SQL to create dynamic package executions on the SSIS Catalog. The code in Listing 17-10 implements the following steps:

1. Declare script variables. Note that in a real-world script, these values would be set through parameters or from an external source.

2. Read the list of files to process from the PackageConfiguration table, and store the results in a table variable (@FileList).

3. Loop through the list of files. For each file, the code will do the following:

a. Retrieve the ID and parameter values from the table variable.

b. Create a new SSIS Catalog package execution.

c. Set the parameter Directory and FileName parameter values.

d. Start the execution.

e. Update the PackageConfiguration table to mark that the file has been processed.

Listing 17-10. Dynamic Package Execution Script

DECLARE @FolderName NVARCHAR(50) = N'ExecutionDemo'
DECLARE @ProjectName NVARCHAR(50) = N'ETL'
DECLARE @DirectoryParameter NVARCHAR(50) = N'Directory'
DECLARE @FileNameParameter NVARCHAR(50) = N'FileName'
DECLARE @PackageName NVARCHAR(100) = N'LoadCustomers.dtsx'

DECLARE @FileList TABLE
(
RowNum smallint,
Id int,
Directory nvarchar(255),
Name nvarchar(255)
)

INSERT INTO @FileList (RowNum, Id, Directory, Name)
SELECT ROW_NUMBER() OVER (ORDER BY id), id, Directory, Name
FROM [dbo].[PackageConfiguration]
WHERE processed = 0

DECLARE @maxCount int = (SELECT MAX(RowNum) FROM @FileList)
DECLARE @count int = (SELECT MIN(RowNum) FROM @FileList)

WHILE (@count <= @maxCount)
BEGIN

DECLARE @Id NVARCHAR(255) = (SELECT Id FROM @FileList WHERE RowNum = @count)
DECLARE @DirectoryValue NVARCHAR(255) = (SELECT Directory FROM @FileList WHERE RowNum = @count)
DECLARE @NameValue NVARCHAR(255) = (SELECT Name FROM @FileList WHERE RowNum = @count)

-- Create the package execution
DECLARE @exec_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@execution_id = @exec_id OUTPUT,
@package_name = @PackageName,
@folder_name = @FolderName,
@project_name = @ProjectName

-- Set the Directory parameter value
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id = @exec_id,
@object_type = 20,
@parameter_name = @DirectoryParameter,
@parameter_value = @DirectoryValue

-- Set the File Name parameter value
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id = @exec_id,
@object_type = 20,
@parameter_name = @FileNameParameter,
@parameter_value = @NameValue

-- Start the package execution
EXEC [SSISDB].[catalog].[start_execution] @exec_id

-- Return the execution ID
SELECT N'Started package execution ' + CONVERT(nvarchar(20), @exec_id)

-- Mark the file as processed
DECLARE @UpdateSql nvarchar(1024) = N'UPDATE [dbo].[PackageConfiguration] SET processed = 1
WHERE id = ' + CONVERT(nvarchar(20), @Id)
EXEC sp_sqlexec @UpdateSql

SET @count = @count + 1
END

Conclusion

This chapter has covered some of the usage patterns for the new parameter model, as well as some dynamic configuration scenarios. Although the configuration patterns and best practices that were commonly used in SQL 2005 and 2008 continue to work in the latest version of SSIS, most users will see a benefit in migrating to the new model. The clarity of the parameter model was designed to help everyone involved with an SSIS solution life cycle, from those who develop the packages to those who deploy and schedule them.