SSIS variable connection manager

Question

SSIS variable connection manager

SQLAssAS

SSCertifiable

Points: 7246
More actions
September 2, 2014 at 3:22 am

#397285

I have a table which contains a column ([Connection String]) that has every connection string for the SQL instances on our estate.
I have a package which will import data from a single instance, but how do I set the connection manager so that it uses a variable and is populated by a selecting the connection strings from my central database?
Do I use a cursor to select the next connection string?
Any advise would be appreciated

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 1

Just to add to the above..

The below would be the cursor I would have, then at then Begin is where I would want the data flow task to connect to @connection and run the SQL command in my OLE DB source. I am not sure how this works in SSIS

DECLARE ConnectionManager CURSOR

FOR SELECT [Connection String] FROM Instance_details

OPEN ConnectionManager

FETCH NEXT FROM ConnectionManager into @Connection

while @@Fetch_status = 0

BEGIN

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Don't use a T-SQL cursor to do this. Use a ForEach loop container in SSIS. You can use the results from your table to set the value of a variable, whcih you can use to set any property of the connection manager, in particular the connection string.

John

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

An example of a dynamic connection string using variables and expressions:

SSIS Dynamic Connections Part 1[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

Thanks guys, I think i have set the variables up right and data flow task etc..

The expression - Do I have to have it like

“Data Source=”+ @[User::DemoSQLServer] +”;User ID=”+ @[User::DemoSQLUserName] +”;Provider=SQLNCLI10.1;Initial Catalog=” + @[User::DemoSQLDatabase] + “;Password=” + @[User::DemoSQLPassword]"

or can i just pass it the variable ( @[User::Connection] ) which contains the connection string? Would that just use my credentials when running the package. I ask the question because its not working..

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

SQLAssAS (9/2/2014)
I ask the question because its not working..

It's not working how? Do you get an error?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 6

error attached. I am assuming the string I am trying to pass just isn't getting to the variable.

If there are any particular screen which may help diagnose the issue let me know and I will screen shot it.

Its the OLE DB Source which has the red X on it. Attached that for info

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 7

Could this be because my connection string I am passing in are just Server\instance ?? and not full qualified connections strings such as..

Data Source=<Server>;Initial Catalog=<Database>;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False

or should Server\instance work as I am using AD Auth?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

That could well be it. Here are two things you can try:

(1) Use this site[/url] to check that the syntax of your connection string is correct

(2) Create another variable, called ConnectionString, in your package, and set it to take its value from an expression. Use the expression that you posted earlier to set its value. Configure your connection manager to use that variable to set the connection string property. Finally, use a message box to display the value of the variable as soon as it has been set, so that you can check that it looks right. (I think there are other ways of inspecting variable values in mid-execution as well - choose your favourite.) Don't forget to lose the message box as soon as you finish testing!

John

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 9

I have just tried passing it sample from the table with a fully qualified connect string

Provider=SQLNCLI11;Server=Server\Intstance;Database=DBA;

Trusted_Connection=yes;

I know its going to be a simple issue but I can't find it! I will have a look at the message box, cheers