Dynamically Setting a Variable from a Connection String that has been set by a Config File

Hi Guys

I'm setting up a Master / Slave (Parent / Child) dtsx environment but I'm unable to work out how to dynamically set a variable in the Master dtsx from a connection string that has had its value set by a config file. I'm sure it's possible.

Below is the what I'm hoping to achieve. I've set up everything apart from the highlighted section.

Any ideas?

March 26th, 2015 11:47am

First, what version of SQL Server are you using?

You could switch the problem around.  You could set the value of a variable from the config file, then it is easy to use that variable as the connection string source for your connection manager.  At the same time you can use a parent variable configuration to map that variable to variables in your child package.

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 12:21pm

Just re-use the same config file in the master and children packages, there is no need for complexities like that.
March 26th, 2015 12:35pm

Hi Russ

It's SQL2008 R2.

The business already uses the config file to connection string method. It's present in all of their SSIS solutions and they don't want to deviate.

Do you know if there's a way of dynamically setting the variable from the connection string (reversing the standard of variable to connection string)?

Thanks

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 2:04pm

I'm afraid that's not an option open to me.

March 26th, 2015 2:04pm

I use a script task to load the connection string into a variable.  The problem is that some information like passwords are not included.

Dts.Variables["User::ConnectionString"].Value = Dts.Connections["MyConnection"].ConnectionString;

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 6:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics