Changing config table location on the fly

Hi everyone,

I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.

The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.

For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1\DatabaseSource and Server1\DatabaseDestination and on server2, the configs point the source to Server2\DatabaseSource and Server2\DatabaseDestination and for server 3, the same thing but pointing to server3.

There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?

When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.

Am I missing something here?

Thanks,

Rob

You use an XML file to set the "config database" location dynamically. You can't use a SQL Server based config table to control where the "config database" resides because that is what you're trying to change.

So, use an XML file, place it on all of the servers, each pointing to the correct "config database" for that environment.|||

Rob,

it sounds like you want to decide, at execution-time, which set of configurations it should use. Right?

So, in essence you need a way of "parameterising" your package so it needs which set of configurations to use. Right?

The best way to do this is to use the /SET option of dtexec.exe

Regards

Jamie

|||

Thats the one!

Thank you Jamie

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Hmmm, I wonder if the order in which things get applied is a problem? Davide Mauri talks a little avout this here: http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx but I don't think it'll help in your case.

Sorry. I'm a bit stumped. Phil...any ideas?

-Jamie

|||

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

|||

r4enterprises wrote:

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

Make sure that you have that config file listed under "Package Configurations" and that you have package configurations enabled. It works, trust me! I use it all of the time. If it doesn't work for you, then something is wrong.

Know that you must use an absolute file path when pointing to the XML config file.|||If you're going to change the connection string of a connection, I'd use the /CONN switch.|||

Phil,

thanks for your insight. the absolute path might be the problem as when I would build the package, I'd go change the config settings of the xml file that is found in the /bin/Deployment directory and not in the project's folder. I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

Please note that when you execute a package with the /conn, that didn't seem to work either. its still pointing to its original location.

I'll try a combination of things when I get to that and keep you guys posted.

Thanks for the help,

Robin

|||

r4enterprises wrote:

I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

What if the package is stored in SQL Server? What is a "relative path" and what should it point to? This is why absolute paths are required.sql