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 18-3) by clicking on the Expressions property in a Task’s Properties window. Expressions can be set on variables directly from the Variables window (as shown in Figure 18-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 18-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 is 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 not all component properties are expressionable. Figure 18-5 shows how expressionable properties on a Lookup Transform “bubble up” and appear 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 18-6), all parameters appear under the “Variables and Parameters” folder.

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 18-7), which acts as a shortcut for making use of parameters in your packages. From this UI, you can create a new parameter, or make use of one that already exists. When you click OK, SSIS will automatically add an expression to the selected property. To launch the Parameterize UI, right click on and select “Parameterize” from the context menu.

Creating Visual Studio Configurations

Another new SSIS feature in SQL Server 2012 is the use of Visual Studio Configurations, which allow you to create multiple sets of parameter values within BIDS. 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 multi-developer or team environments.

When you first create a project within BIDS, you will have a default configuration called Development. You can create additional from the Configuration Manager dialog (shown in Figure 18-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 drop down.

To add a parameter to a configuration, click the Add Parameters to Configurations button on the package Parameters tab. Figure 18-9 shows the Manage Parameter Values dialog that will be displayed when adding 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 only package parameters and project parameters can be added 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.

Specifying Entry Point Packages

SQL Server 2012 introduces another new concept for SSIS – Entry-point Packages. 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 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.

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 18-10).

Figure 18-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 parameter or connection manager property, click the ellipse button at the end of the row. You will have three options when changing 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.

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 is it currently running in.

Before you can map a value to server environment variable, you must associate the environment with the project. Figure 18-12 shows the References tab of the project configuration dialog which allows you to associate a project with one or more 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.

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 clicking the Script button. Listing 18-1 shows the T-SQL used to set default values for a two items; a package parameter (MaxCount) is to set 100, and a connection manager property (CM.SourceFile.ConnectionString) is set to 'C:DemosDataRaggedRight.txt'.

Listing 18-1.  Setting parameter values using T-SQL

DECLARE @var sql_variant = N'C:DemosDataRaggedRight.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

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 18-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 DTEXEC.

The Execute Package UI also has a Script button, which allows you to script out the creation of a package execution to T-SQL. Listing 18-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 18-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 Agent has 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 provides the same configuration options. Alternatively, you can run SSIS packages using the T-SQL job step. However, as 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.

Projects on the File System

While the new Project Deployment Model is primarily meant for use 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 /Set option, and /ConfigFile can be used to set a number of parameter values from a 2005/2008 style XML configuration file. Table 18-1 provides a summary of the options related to running packages from projects stored on the file system.

Listing 18-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 18-3.  Running Packages Within a Project File Using DTEXEC

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

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 18-2 contains a full list of command line options for SSIS Catalog based execution with DTEXEC.

Listing 18-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 can be found below.

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

C: > dtexec.exe /Ser ETLServer /IS SSISDBMyFolderETLProjectMyPackage.dtsx /Par
$Project::BatchNumber;432 /Par $Package::HostName;localhost /Par
"$ServerOption::SYNCHRONIZED(Boolean)";True
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 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

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 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 18-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.

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 how to retrieve values from a database table using an Execute SQL Task and 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.

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
)

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, 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 18-9 provides sample code a Script Task that sets a Connection Manager’s ConnectionString at runtime.

Listing 18-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 is 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 18-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 18-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:

   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 18-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 @PackageList 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