How do you manage package configurations at scale in an enterprise ETL environment with SSIS 2014?

Question

I'm migrating some packages from SSIS 2008 to 2014. MS is touting moving to project deployment and using SSIS environments for configuration because it's more flexible, but I'm not finding that to be the case at all.

In previous versions, when it came to configurations, I used a range of techniques. Now, if I want to use project deployment, I'm limited to environments.

For those variables that are common to all packages, I can set up an environment, no problem. The problem is those configuration settings that are unique to each package. It seems insane to set up an environment for each package.

Here is the question: I have several dozen packages with hundreds of configuration values that are unique to the package. If I can't store and retrieve these values from a table like in 2008, how do you do it in 2014?

Vinnie Vinnie · Accepted Answer · 2015-09-22T01:58:55

That's not necessarily true about only being able to use environments. While you are limited to the out of the box configuration options, I'm working with a team and we've been able to leverage a straightforward system of passing variable values to the packages from a table. The environment contains some connection information, but any variable value that needs to be set at runtime are stored as row data.

In the variable values table, beside the reference to the package, one field contains the variable name and the other the value. A script task calls a stored proc and returns a set of name/value pairs and the variables within the package gets assigned the passed in value accordingly. It's the same script code for each package. We only need to make sure the variable name in the table matches the variable name in the package.

That coupled with the logging data has proven to be a very effective way to manage packages using the project deployment model.

Example:

Here's a simple package mocked up to show the process. First, create a table with the variable values and a stored procedure to return the relevant set for the package you're running. I chose to put this in the SSISDB, but you can use just about any database to house these objects. I'm also using an OLEDB connection and that's important because I reference the connection string in the Script Task which uses an OLEDB library.

create table dbo.PackageVariableValues
(PackageName NVARCHAR(200)
, VariableName NVARCHAR(200)
, VariableValue NVARCHAR(200)
)

create proc dbo.spGetVariableValues
@packageName NVARCHAR(200)
as
SELECT VariableName, VariableValue
FROM dbo.PackageVariableValues
WHERE PackageName = @packageName

insert into dbo.PackageVariableValues
select 'Package', 'strVariable1', 'NewValue'
union all select 'Package', 'intVariable2', '1000'

The package itself, for this example, will just contain the Script Task and a couple variables we'll set at runtime.

I have two variables, strVariable1 and intVariable2. Those variable names map to the row data I inserted into the table.

Within the Script Task, I pass the PackageName and TaskName as read-only variables and the variables that will be set as read-write.

The code within the script task does the following:

Sets the connection string based on the connection manager specified
Builds the stored procedure call
Executes the stored procedure and collects the response
Iterates over each row, setting the variable name and value
Using a try/catch/finally, the script returns some logging details as well as relevant details if failed

As I mentioned earlier, I'm using the OLEDB library for the connection to SQL and procedure execution.

Here's the script task code:

public void Main()
{
    string strPackageName;

    strPackageName = Dts.Variables["System::PackageName"].Value.ToString();

    string strCommand = "EXEC dbo.spGetVariableValues '" + strPackageName + "'";

    bool bFireAgain = false;

    OleDbDataReader readerResults;

    ConnectionManager cm = Dts.Connections["localhost"];
    string cmConnString = cm.ConnectionString.ToString();

    OleDbConnection oleDbConn = new OleDbConnection(cmConnString);

    OleDbCommand cmd = new OleDbCommand(strCommand);
    cmd.Connection = oleDbConn;

    Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "All necessary values set. Package name: " + strPackageName + " Connection String: " + cmConnString, String.Empty, 0, ref bFireAgain);

    try
    {
        oleDbConn.Open();
        readerResults = cmd.ExecuteReader();

        if (readerResults.HasRows)
        {
            while (readerResults.Read())
            {

                var VariableName = readerResults.GetValue(0);
                var VariableValue = readerResults.GetValue(1);

                Type VariableDataType = Dts.Variables[VariableName].Value.GetType();
                Dts.Variables[VariableName].Value = Convert.ChangeType(VariableValue, VariableDataType);

            }

            Dts.Events.FireInformation(0, Dts.Variables["System::TaskName"].Value.ToString(), "Completed assigning variable values.  Closing connection", String.Empty, 0, ref bFireAgain);
        }
        else
        {
            Dts.Events.FireError(0, Dts.Variables["System::TaskName"].Value.ToString(), "The query did not return any rows", String.Empty, 0);
        }
    }
    catch (Exception e)
    {

        Dts.Events.FireError(0, Dts.Variables["System::TaskName"].Value.ToString(), "There was an error in the script.  The messsage returned is: " + e.Message, String.Empty, 0);
    }
    finally
    {
        oleDbConn.Close();
    }
}

The portion that sets the values has two important items to note. First, this is set to look at the first two columns of each row in the result set. You can change this or return additional values as part of the row, but you're working with a 0-based index and don't want to return a bunch of unnecessary columns if you can avoid it.

            var VariableName = readerResults.GetValue(0);
            var VariableValue = readerResults.GetValue(1);

Second, since the VariableValues column in the table can contain data that needs to be typed differently when it lands in the variable, I take the variable data type and perform a convert on the value to validate that it matches. Since this is done within a try/catch, the resulting failure will return a conversion message that I can see in the output.

            Type VariableDataType = Dts.Variables[VariableName].Value.GetType();
            Dts.Variables[VariableName].Value = Convert.ChangeType(VariableValue, VariableDataType);

Now, the results (via the Watch window):

Before

After

In the script, I use fireInformation to return feedback from the script task as well as any fireError in the catch blocks. This makes for readable output during debugging as well as when you go look in the SSISDB execution messages table (or execution reports)

To show an example of the error output, here's a bad value passed from the procedure that will fail conversion.

Hopefully that gives you enough to go on. We've found this to be really flexible yet manageable.

How do you manage package configurations at scale in an enterprise ETL environment with SSIS 2014?

2 Answers

Example:

Approach 1

Approach 2

Option A

Option B