Sunday, March 25, 2012

Configuration Changes

We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.

Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.

I think this is possible, yeah. If your packages need to be executed in different scenarios then you effectively need to "parameterise" those packages. Is that correct?

If so, you should lookat using the /SET option of dtexec.exe. It is provided for just this situation.

Configurations aren't really what you after. They are more about making your packages location independent, whereas it seems to me that you want to parameterise your packages.

Good luck with it.

-Jamie

|||

Thanks. We'll have to think through this a bit. *If* we could change the filter at package invocation then configurations would fit our needs perfectly. We'll probabbly look for a way to set several SSIS variables from a SQL table and use a /SET to specify which set of values to populate variables from.

I think we could pull it off with configurations using multiple tables, but I'm not fond of that either. I guess it's rough to be picky. :)

|||That's the point of configuration tables in SQL Server though -- to make things flexible...

Perhaps you could store your multiple configurations in a table and then with a stored procedure or something, update the SSIS Configuration table with the appropriate values.|||

We actually do that for sub packages, but it doesn't seem appropriate here. If I update the single set of SSIS values from two different jobs at close to the same time, it's likely one of them will run with the wrong values. Tracking that down seems like more effort than I'd like to spend if it occured.

My colleague had an idea we're investigating. Using environment values. We're thinking that perhaps by creating a batch file that sets a value on the fly and then issues a dtexec that we might achieve the result we want without putting values permanently into the evironment or exposing sensitive data.

|||

Ok, I think we have it working. We created a batch file called SSISWithConfiguration.bat:
SET env="Configuration";"[dbo].[SSIS Configurations]";%2
dtexec /DTS %1

In the job we just pass the path to the SSIS package and the Configuration filter values we want to use. The environment variables will be separate for each invocation and we only have one batch file to deploy ever. (We always call a configuration connection called Configuration). Execution looks like:
C:\MSSQL\SSISWithConfiguration.bat "\MSDB\folder\packageName" "New filter name"

In the package we just create an indirect SQL Server configuration with the environment variable as env.

During development we can use configuration settings directly and then when ready to deploy a reusable package switch to indirect (that way we don't have to have permanent environment variables on the developers desktops and the configuration table in development will get updated).

Finally, in the package, we have an XML file configuration listed first to move the Configuration connection between development, test, and production. This gets deployed once on each developers box and once on each server.

The last piece we wrote was a small utility that will let us pick a configuration setting and will write the SQL script to insert the configuration settings after deleting any existing values. We save that three times and change the values for development, test, and production.

Thanks for all the ideas and support. Sometimes it's just looking at stuff a bit different :)

No comments:

Post a Comment