Configuration Order

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.

Does anyone have a solution for this

I would still need to be able to set a modifier to say which configuration location I wanted to use.

I have the same package that I can use to load the same file from different customers. Obviously I want the data to end up in different databases, possibly even onto different servers. So far I have a seperate SQL configuration database for each customer, however I can only tell the package to look at one configuration database, even if I use indirect configuration, per server. And their lies the only answer I have at the moment have one configuration per server, instead of managing multiple customers in different services on the same hardware.

It would have been nice to be able to set the connection string for the configuration database from outside the SSIS package and have it applied before the other configurations kicked in. The effect I get at the moment is even though I use SQL Agent to set the connection string of the connection maanger I am using to access the SQL configuration, it seems to be applied after the other configurations are set and subsequently the other SQL configurations do not know where to get their properties from.

I hope I am wrong about this, and I have just missed something simple.

I understand your problem; but not sure about the solution. Have you tried the 'SET' option available in the DTEXEC utility to override the connection string that points to your configuration table For this you would have to use a CmdExec step in your SQL Server agent (which BTW gives you better logging information)

Philip Coupar wrote:

I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.

At some point, are you using in your packages 'Parent Package variable' for setting any of the configurations If so, that may be the problem. There is a bug with configurations using 'parent package variables'.

I am currently using XML file to set up the connection string of my SQL Server configuration table with no problem; I have done the same using Environment variables; if this is what you are doing the problem must be something else.

I had started out using Parent Package configuration until I discovered that small issue.

I now have an XML configuration file, which is working fine. However I would like to have the same packages used in multiple configurations on the same machine even using indirect configuration I can only point my packages to one xml configuration file, and even if I try to override the xml configuration file by using configurations in SQL Agent that is only apllied after the original configurations have been applied and subsequently my internal SQL configurations are missed.

It would be nice if configurations applied in SQL Agent were applied first or you could specify the order in which ALL of the configurations both internal and external were applied.

It would also be nice if parent package configurations applied in the order they were presented and not last as they do at the moment, in fact I think they are applied after validation.