SSIS Question - In the EXECUTE SQL TASK how can we make the connection dynamic to be assigned at run time.

Question

SSIS Question - In the EXECUTE SQL TASK how can we make the connection dynamic to be assigned at run time.

mw_sql_developer

SSCoach

Points: 19445
More actions
September 18, 2015 at 6:24 am

#321265

I thought I could add a paremeter and set this value:
MHPDEV;Initial Catalog=P4P;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
Then you can set an expression so that the CONNECTION property will take the value in the PARAMETER. I did that but it did not work

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Are you trying to set the Connection property of the Execute SQL Task or the ConnectionString property of the Connection Manager?

What I believe you want to do is set the ConnectionString property of the ConnectionManager (this is what you actually use in the Execute SQL Task Connection).

If you want to change the Connection for the Execute SQL Task you would need to have multiple connection managers and then assign the correct one using an Expression to set the Execute SQL Task Connection Property.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Ahhh good point!

>>>>

Are you trying to set the Connection property of the Execute SQL Task or the ConnectionString property of the Connection Manager?

What I believe you want to do is set the ConnectionString property of the ConnectionManager (this is what you actually use in the Execute SQL Task Connection).

<<<

So how would you do that ?

Have several parameters and assign the connection string(s) to them.

Then change the connection string by using an expression ? Right!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Without knowing all the requirements and what exactly you need to do it is hard to say how I would do it. Typically when I write an SSIS package at execution time the connections are set for the length of the execution and I use configuration (pre-2012) or environments to set the connection strings. Your questions seems to indicate that you have a SQL Statement that you want to run against multiple databases in a single package execution, which is not something I have done. My first instinct says to have a single variable for the connection string and change the value of the variable in loop. My package would look something like this:

Paramaters:

Variables:

ExecutionConnectionStringList - an object variable to hold the list of connection strings

Connections:

ExecutionConnection - used for the main execute sql task and set in a loop

Tasks:

For Each Loop - Loop of over the ExecutionConnectionStringList populating the ExecutionConnectionString variable

Execute SQL Task - Execute the Main SQL

[/ul]

I think this would work, but I haven't done dynamic connection strings this way before, so be sure to test it thoroughly. You'd also want to add some error handling and logging. What do you do if the main EST fails for one connection string? Does the package fail? Do you log the failure and error message and continue?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question