Posts tagged ‘Configurations’

Passing an Object from a Parent Package To a Child

Occasionally, you may run into the need to pass values between packages. In most cases, you can use a Parent Package Variable configuration to pass the value of a variable in the calling (or parent) package to the called (or child) package. However, Parent Package Variable configurations can’t be set up on variables of type Object. Fortunately, you can work around this pretty easily, thanks to the fact that the calling package variables are inherently accessible in the called packages. 

I’ve set up a sample parent and child package to illustrate this. The parent package is straightforward. It uses an Execute SQL task to populate an object variable named TestVar with a recordset object, and then calls the child package.

image image

The child package has a Foreach Loop Container to iterate over the recordset object. It has a Script task that is used to copy the parent package’s variable (TestVar) to a local variable named LocalVar. This is the variable that the Foreach Loop is configured to use. Why copy the value? If you don’t have a local variable to reference in the Foreach Loop, it won’t validate properly.

image image

The script in Copy Variable is pretty simple. It relies on the fact that you can reference parent package variables inherently, as they are included in the collection of variables accessible in the local package. The script just copies the value from one variable to the other, so that the Foreach Loop will have something to do.

public void Main()
    Variables vars = null;
    Dts.VariableDispenser.GetVariables(ref vars);

    vars["User::LocalVar"].Value = vars["User::TestVar"].Value;

    Dts.TaskResult = (int)ScriptResults.Success;

Please note that for this to work, you cannot have a variable in the child package with the same name as the variable in the parent package. If you do, the local variable will hide the parent variable. Outside of that, this works really well for passing object values between packages. The same technique can also be used in reverse to send values back to the parent package, if you have that need.

The sample has been uploaded to my Skydrive. Let me know if you have any questions.

Configuration Approaches In SSIS

A number of the SSIS MVPs were asked to contribute a white paper to MSDN a few months ago, and those articles were published recently. You’ll notice a common thread in most of the topics:

They are all great articles, and well worth reading.

Judging by the volume of questions related to configurations on the MSDN Forums, they are one of the more confusing features of SSIS. While there is some overlap in the articles on configurations, there’s valuable information that is unique to each, and will help give you a more rounded view of how to use them. As there are a lot of options and tradeoffs with configurations, it’s no surprise that everyone uses them slightly differently.

I hope the articles are valuable. Please leave comments if you have an approach to configurations that works well for you.

Presenting at Midlands PASS

I’m presenting at the Midlands PASS chapter in Columbia, SC tomorrow night, July 17th. I’ll be presenting on SSIS configurations and some common approaches for implementing them. Configurations are one of the best features about SSIS, but also one of the most confusing. If you’re in the area, and would like to learn more, please stop by.

Configuration Changes in SSIS 2008

In a previous post, I detailed some of the challenges that I had encountered when trying to modify the connection string used for SQL Server configurations from the command line. In SSIS 2005, command line options were applied after the configurations were loaded (with the exception of parent package variables). Effectively, this meant that you could not change the location a configuration pointed to from the command line.

This has been changed in SSIS 2008. Configurations are now loaded twice, once when the package is originally loaded, and then again after any command line options are applied. The order of events is:

  1. The package file is loaded.
  2. The configurations specified in the package at design time are applied in the order specified in the package (except for Parent Package Variables).
  3. Command line values are applied.
  4. The configurations specified in the package at design time are reloaded in the order specified in the package (except for Parent Package Variables). The configurations will use any updated values from the command line when they are reloaded, so it is possible to change the connection string for a SQL Server configuration.
  5. Parent Package Variable Configurations are applied.
  6. The package is run.

This is a nice improvement, since you can now set up a package with SQL Server configurations, and point the package to the correct database are runtime by simply using the /CONN switch of DTEXEC. However, it still isn’t perfect . Since the design time configuration is applied twice, a value that you apply using /SET on the command line can be overridden by a preexisting configuration.

If you’d like to see this behavior, I’ve provided a simple package. The package has a single variable (“TestVar”) defined. It has an XML configuration enabled that sets the value of TestVar to “Dev”. There is a single Script Task that fires an information event that contains the value of the variable.

public void Main()
            bool fireAgain = false;
            Variables vars = null;
            Dts.VariableDispenser.LockOneForRead("TestVar", ref vars);
            Dts.Events.FireInformation(0, "", "Value is: " + vars["TestVar"].Value, "", 0, ref fireAgain);
            Dts.TaskResult = (int)ScriptResults.Success;

If I run this package using this command:

dtexec /file Configuration_Overwrite.dtsx /SET \Package.Variables[User::TestVar].Properties[Value];Prod /REP EWI

I’d expect to see the value “Prod” reported in the output. Instead, “Dev” is displayed. If you disable the package configurations and re-run it using the same command, it does report “Prod”.

Overall, I prefer the new behavior, but it does introduce a new issue to be aware of. In general, you need to be careful to not /SET a value that is also specified in a configuration, as it will be overwritten. I’ve filed a Connect suggestion for the addition of /SetBefore and /SetAfter switches to DTEXEC, so that you can explicitly define when you want the /SET applied. If you think it’s a good idea, please vote for it.

The sample package is on my SkyDrive. You may need to update the location of the XML configuration, but it has no other dependencies.

Thanks to Cho Yeung on the SSIS team for clarifying the load order when the package is executed.

SSIS Configurations At Design Time

There’s an interesting “behavior” that occurs with configurations at design time. The value of the configuration will be saved in your package as the new stored value. I’d seen this before, but never really thought about it, until a poster on the forums questioned why it was happening. To understand it, you need to know how the package is processed when it is loaded in the design environment.

If you have configurations enabled in the package, then the configuration is applied to the package when it is initially loaded. When a configuration is applied, it updates the target property of the configuration. For example, you might have a package that has the Description property set to “No Configuration”. If you add a configuration that sets the Description property of your package to “Has Configuration”, when the package is loaded, the value specified in the configuration (“Has Configuration”) will overwrite the value currently stored in the Description property (“No Configuration”).

If you then close the package without saving, the change to the property is not saved, and it will reflect the original value (“No Configuration”). If, however, you save the package (and remember, running it in the IDE saves it automatically), the new value (“Has Configuration”) will be saved as the value in the package file.

Normally, this does not cause any issues. If you execute a package in production that has a configuration, it is not saved after execution, so the values of the configurations is not save in the package file. However, it can impact you in the development environment. This is one of the reasons that I recommend setting up configurations even in your development environment. If you do that, then you know that regardless of the value the package was saved with, it will have the value of your local configuration each time it is loaded in the IDE. This is also a good reason never to update a package directly on your production server. When you open it in the IDE on the production server, it will retrieve the production configurations. If you save or run it, it will be saved with the production configuration values. This can cause a number of headaches in troubleshooting problems down the road, so I recommend that you don’t do it.

Fun with SSIS Configurations

As part of my standard approach (and the one used by Mariner), I use configurations in SSIS quite heavily. My preference is to use the SQL Server configuration type, since it keeps all the configurations in one place. XML configurations tend to result in a lot of extra files to manage. With SQL Server based configurations, though, you need to provide the initial connection string to the SQL Server. We typically use a system environment variable to control that initial connection to the configuration table (either directly, using a specific configuration to control the connection as Rafael has show in the past, or using the indirect approach, as highlighted by Jamie). We use a separate connection manager for the SQL Server configuration connection, usually named Configuration (I like self-explanatory names).

One of our current clients has their development and QA databases as separate SQL Server instances on the same server. There a number of reasons why I don’t care for that approach, but that’s another post. We also needed to use this same server as the host for both the development and QA versions of the SSIS packages. This posed a problem, as the both versions of the package would pick up the same environment variable, so we would only be able to run for one environment at a time.

My initial thought was to use the /CONN switch of DTEXEC utility to override the Configuration connection. Unfortunately, this didn’t work. As best as I can tell, based on some information here and my own testing, the order of events at package load time is:

  1. Configurations are applied, based on what is set in the package at design time.
  2. Any /ConfigFile, /Connection, and /Set values are applied.
  3. Package validation is run.
  4. The package is executed.

Basically, what this boils down to is that there is no way to override a SQL Server configuration from the command line. Even setting the Configuration connection manager from an XML configuration on the command line doesn’t work, because by the time it is applied, the design time configuration is has already been set. Setting the connection string doesn’t force the configurations to be reapplied.

There are a few workarounds for this. You could apply an XML configuration at runtime to override all the configured values in the package, but that defeats the purpose of using SQL Server configurations. We ended up with a different approach, that still lets us use the SQL Server configurations. By setting the environment variable up as a user variable, we can override it based on the user account that the packages are run under. This has a few caveats as well. For one, you need a separate account for each unique environment (not unnecessarily a bad thing). Another is that you can’t use the Integration Services job step type to run the package. Evidently, it doesn’t create a full user session, which is needed to access user environment variables. You can work around it by using the CmdExec job step type.

If you find yourself needing to support multiple environments on the same server, using configurations can be difficult. Hopefully, this information will be helpful for determining the best approach.