Kirk Haselden

Package command line configurations...

I've been seeing questions about this and although I've posted about configurations before, here command line configurations are a bit different.

There are some cases where you wish to pass values for properties directly on the command.

Here is how to do it:

DTExec /f PackageName.dtsx /set \Package\ForLoop\Sequence.Variables[VariableName].Value;VariableValue

This package path (bold) points to the variable named “VariableName“ defined on a sequence named “Sequence“ inside a ForLoop container named “ForLoop“.

Containers are delimited by the '\' token. Properties are delimited by a period. And indexes into collections are delimited by brackets [].

Another way to easily figure out a package path is to create an XML configuration to a property and then copy the package path directly from the configuration file. For example, an XML configuration to configure a package with a variable called “test“ at package scope would look something like this:

<Configuration ConfiguredType="Property" Path="\Package.Variables[User::test].Value" ValueType="String"><ConfiguredValue>Default</ConfiguredValue></Configuration>

Cut the “Path“ portion out of the file and paste it into the command line, and you can configure the variable through DTExec.

DTExec /f MyPackage /set \Package.Variables[User::test].Value;“This is the new value of the variable“

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden
 

Package Configurations II

In an earlier blog I wrote a little about package configurations. IDW11 (December CTP) had limited support for SQL Configurations. That will be changing in the next CTP build (IDW13), slated for sometime in Feb. You will no longer need to script a table for the configurations. Support will be built into the Package Configuration wizard. It looks something like this:

You'll still need to insert the configuration values as described in the earlier blog post, but the wizard enhancement should make it much easier to set these up.

Someone also asked how the configuration name is used. It's placed in a ConfigurationFilter column in the SQL Configuration Table. Essentially, the SQL Configuration uses this to query the table and extract a subset of configurations. Using this field you can create multiple configurations for the same package or set of packages. Or, use it to simply group configurations for a particular solution etc.

Look for this change in the IDW13 build and let us know what you think.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

One configuration, multiple packages...

This question comes up a lot.

“Can you use one configuration for multiple packages?“ The answer is yes. In fact, from the start, this was the scenario that we were planning for and ultimately is the way to abstract your packages away from the differences in the machines they are running on. Here's a really simple example that shows how to configure a property on a task, in this case, the subject line of a send mail task in two packages with the same configuration file:

Here's how you do it:

1. Create two new packages in your solution.
2. Drop a send mail task into each one
3. Rename them both, SendMail. I know, not much imagination. Bear with me, it's late.
4. Change the subject lines for both Send Mail tasks to something random like “On these three breezy trees the cheezy flees flew“
5. Right click on one of the packages and select configurations.
6. Create a new XML configuration file and specify the subject property of the SendMail task to export to the configuration.
7. Save the configuration somewhere.
8. Go to the other package, right click, select package configurations, browse for the one you've just created and select it.
9. The wizard will ask you if you want to reuse the existing one. Click on yes.
10. Now you've got two packages that use the same configuration. So long as both tasks are named the exact same, and they remain in the same location hierarchically (so their package paths match), both will get configured from the same configuration you've created.

This will yield a configuration that should look something like this:

Configuration ConfiguredType="Property" Path="\Package\SendMail.Subject" ValueType="String"

If you try this, let me know how it works for you.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Package Configurations

So, there's been a lot of discussion lately on the newsgroups about package configurations. Package configurations are new in SSIS. They're a way to make changes to your packages without actually editing the packages. They are partially targeted at filling the hole left by the departure of the dynamic properties task, which was deprecated because it simply could not continue to function in the new architecture of Integration Services.

Package configurations come in a few different flavors including, SQL, INI, XML, Registry, Environment variables and parent package. Here's how they generally work. You create a configuration, usually in the package configuration organizer wizard. Select some properties you want configured and click OK. You get there by right clicking on the designer surface and selecting Package Configurations. 

Once you've opened the wizard you can enable/disable package configurations by clicking on the “Enable package configurations” checkbox. This checkbox enables them for the current package only and doesn't effect any other packages, even those in the same solution or project. It's important to understand, also, that all package configurations, with one exception, are applied at load time. So after the package is completely loaded, configurations are applied, not at package execution time. The one exception is Parent Package Configurations. Those configurations are actually applied when the Execute Package task is executed because that's when it's most likely that the parent package variable that's being referenced in the configuration is valid.

Some things everyone should understand about package configurations:

• More than one can be applied to a package.
• One configuration may be applied to more than one package.
• Each package configuration is applied in the order shown in the configuration wizard, except Parent Package Configurations.
• If an entry in a configuration fails, it will only emit a warning. Configurations should never raise an error or cause a package load to fail.
• The location of a configuration may be hard coded in the package, or pointed to from an environment variable. Which may cause you some grief when you deploy packages with configurations. Use indirect configurations (Environment variable contains location of configuration) for best results here.
• Using one configuration file per server is optimal. That's a whole article by itself.

Using package configurations and property expressions, most properties on most tasks may be configured at load time. The exception is pipeline transform and adapter properties. Pipeline property expressions didn't make it in and likely won't in this ship cycle.

Package paths - What's a package path. It's essentially a URN that points to a property on an object in the package. Here are some examples:

\Package.Connections[{E9598474-461E-48F7-B902-52A140B7FE14}].ConnectionString

\Package\LoopOverFiles\sql-TruncateLoadTable.SqlStatementSource

\Package.Variables[::FileLocation].Value

They all start with \Package. The '.' character indicates that a property follows. The '\' character indicates that a container or task follows. The string between the '[' and ']' characters indicate an index into a collection. That index will either be the name of the object or the ID. The ID is safer to use because, unless someone manually edits the package, the ID will never change. The name is more portable because more than one package may have the same named connection for example. That way, you may use the same configuration for multiple packages. For example, a configuration with the third package path above could be applied to all packages that have a variable named FileLocation in the package's variable collection (defined at package scope).

Now, when configurations are applied to a package, IS reads the configuration, finds the property the package path points to, and sets it to the value specified in the “ConfiguredValue“ section of the configuration. One thing you should be aware of though. Once the package is configured, it's just the same as if you had edited the values of each of the properties yourself in the designer. If you save the package after the configuration has been applied, the configured properties will be saved with the new values. It's not necessarily bad or good, it's just good to be aware of it. This is one of the reasons why the enable configurations setting exists, so you can turn off configurations as you're developing your package and avoid such unexpected behavior.

The two types of package configurations that haven't been documented yet in books online is INI and SQL configurations. INI package configurations have basically been provided for backward compatibility. Although, the INI files you used for the dynamic property task are not compatible, it may be a little easier for you to convert them to use INI configurations. I like INI configurations because they're simple and clean. In spite of what some say, and I know I'm going to get flamed for this, but XML isn't for everyone for everything... OK, flame away. :)

SQL Package Configurations are likely the most important because they provide the possibility of a central configuration store for your entire enterprise. Support for SQL package configurations is late, but continuing to improve. IDW11 CTP has incomplete support for SQL Configurations. We're working on this feature and it should get easier to setup in beta 3. If you'd like to start using them now, you'll need to create a table in one of your databases. Here's the script to do setup:

USE ISConfigurations
CREATE TABLE [dbo].[SQLConfigurations]
(
   [ConfiguredValue] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [ConfigurationType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [PackagePath] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [ConfiguredValueType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [ConfigurationFilter] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

INSERT INTO SQLConfigurations VALUES
(
  'C:\temp\mytest.txt',                                                            --Configured Value
  ' ',                                                                             --Configured Type, no longer need
  '\Package.Connections[{EAA98474-461F-48F7-B902-COFFEEBEEF42}].ConnectionString', --Package Path
  'String',                                                                        --Configured Value Type
  '' )                                                                             --Configuration Filter

UPDATE: See related blog here  

SQL Configurations use a connection manager to access the table where the configurations are stored and you may create the table in any of your databases. It's not limited to say, MSDB for example.

You can find more about configurations in books online here: 

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/91ac0347-f908-44f5-bd3d-115790223af4.htm

This is just a quick runthrough. Hope this helps clarify package configurations a bit. Give them a try. Give us some feedback!

Maybe I've just caused you to think of more questions. Let me know.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden