0
votes

SSIS 2008. One parent, many child packages. All the packages connect to two SQL Server 2008 R2 servers. What I want to achieve is one configuration containing the connection details for the two SQL Server (server, database, username, password). So all the packages (parent and children) can use this configuration.

One way I can think of is to set variables for connection details in the parent and all children. And in the children set the variables to use the values from the parent. So I have only one place to set the connection details, which is the parent. But I will need to create variable for every child. I have a dozen of children, so that's a lot of typing and setting up.

So is there a better way to do this?

1
Or you could have all the children packages refer to the configuration directly. Or you could use a registry or environment variable config. If possible try to use windows authentication so you don't need to specify user and password.Nick.McDermaid
I tried package configuration at first. But not every package will use the same connections. Some use both, some use one of them. The problem I found with package configuration is when multiple packages are using the same configuration file, they tend to mess with each other.thotwielder
In what way do they mess with each other?Nick.McDermaid
ok maybe i am not experienced on that. My original thought was one configuration file have all the connections then all packages can refer to that file for the connections needed. But some of the packages do not use all connections, and when I tried to use that configuration file, it asked me to reuse existing configure file or overwrite. I chose reuse then found the file been overwritten with only connections in that package (so connections not used in the package are lost).thotwielder
I later figured out the correct way is to use one file for one connection, and use the same name for the same connection across packages. Then in each package apply only needed configuration files.thotwielder

1 Answers

1
votes

Trying using SQL table configurations. They are easier to maintain and read than a config file. Break each connection into its own configuration so you can apply them to the packages as needed and not apply all of them to every package.

Make sure that the connection managers are named the same in every package - configuration filters are case sensitive.