Dynamic SSIS Configurations

Hey there,

I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.

I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.

I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from.

The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.

Thank you,

Robin

Sure. This is how I do it. I have one XML file that is stored on each server that stores the connection string to the "configuration" database. Then each time we promote the package, no changes have to be made to it.|||

Awesome,

Thank you.

Also, for scheduling purposes, if I did not want to use an xml configuration file, I can use the /SET option with the dtexec.exe command and set the Package.configurationLocation (don't quote me on the name of the configuration) property as well right?

The DBA's over here would rather only have packages and not config files to move over from server to server.

Would that be an option as well?

Of course it would be defined in the documentation that the package expects a certain parameter.

Thanks

|||I can't comment on all of that, but the way we do it here, we create the XML config file once and never change it. So once it's on the server, we never change it. Of course we would have to change it if the database contained in the config file changes to a new name...|||

Again, Thanks for the rapid responses.

Cheers

Rob

|||

r4enterprises wrote:

Awesome,

Thank you.

Also, for scheduling purposes, if I did not want to use an xml configuration file, I can use the /SET option with the dtexec.exe command and set the Package.configurationLocation (don't quote me on the name of the configuration) property as well right?

The DBA's over here would rather only have packages and not config files to move over from server to server.

Would that be an option as well?

Of course it would be defined in the documentation that the package expects a certain parameter.

Thanks

You can use /SET or /CONN (I think) to set the connection strings in the packages through DTEXEC. However, be aware that if you run other packages from the first package, the connection string won't be passed along, unless you use Parent Package variables.

|||

Thats great!

Luckily, I do not use other packages inside the first one so I don't have to worry about it.

However, this is noted and I'll forward this to the documentation group so they make a note.

If I use /SET to set the connection string of the child package, would that work though?