Dynamic connections in SSIS

Question

Dynamic connections in SSIS

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
January 27, 2008 at 10:19 am

#178570

Comments posted to this topic are about the item Dynamic connections in SSIS
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 15 posts - 1 through 15 (of 21 total)

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

Gordon Radley SSCommitted Points: 1776 More actions · Answer 1

I realze option 4 is correct if you use the DTS object model to execute the task, but I use package configurations all the time to change connection values at run time.

Why is option 3 not also a correct answer here?

"Yes - In SSIS connections can be dynamically updated during execution using package configurations."

craigpessano SSCertifiable Points: 5358 More actions · Answer 2

I'm wondering the same thing as Gordon. As is often the case, it seems that there are multiple ways of accomplishing the goal.

Quote from SQL Server 2005 Books Online:

Connection Manager How-to Topics (SSIS)

The properties of connection managers such as the connection string must frequently be updated at run time. To learn more about dynamically updating properties, see Package Configurations and Using Property Expressions in Packages.

Jim Howell Ten Centuries Points: 1010 More actions · Answer 3

Jim Howell

Ten Centuries

Points: 1010

January 28, 2008 at 6:47 am

#772390

I would have to agree that No. 3 is correct also.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Gordon Radley (1/28/2008)
I realze option 4 is correct if you use the DTS object model to execute the task, but I use package configurations all the time to change connection values at run time.
Why is option 3 not also a correct answer here?
"Yes - In SSIS connections can be dynamically updated during execution using package configurations."

The question wasn't can you change them at run time .. which is what the package configurations are for .. but during execution. Ie can you use the same connection to hit multiple servers during the same execution of the package.

I could certainly be wrong but my understanding of package configurations is that they only happen when you initially run the package.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Jeff Deluca Ten Centuries Points: 1152 More actions · Answer 5

I'm not sure if this helps anyone but I found this article.

I've never done it this way myself.

http://msdn2.microsoft.com/en-us/library/ms136093.aspx

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

Also just to point it out, part of what I was trying to get at was that you could create a loop in your package to run through a list of servers, files, etc and inside the loop put a script object that changes your connection string. Then create any other object that uses that connection.

I've used it to run the same script on a series of servers and to import data from a series of similar files. In one instance I used it it to let me test a connection where I had just changed the password to a random string.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Gordon Radley SSCommitted Points: 1776 More actions · Answer 7

Fair Enough!!

Obviously my interpretation of the question was a bit off..

Thank you!

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 8

Gordon Radley (1/28/2008)
Fair Enough!!
Obviously my interpretation of the question was a bit off..
Thank you!

Gordon,

Based on the most popular "incorrect" answer, I'd say it was more than just you interpreting the question a different way. If the question author meant "multiple servers connecting to the same OLEDB connection once the package has started", it should have been worded so. Otherwise, package configurations are the natural way to change OLEDB connections at runtime.

Vince

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 9

viacoboni (1/28/2008)
Gordon Radley (1/28/2008)
Fair Enough!!
Obviously my interpretation of the question was a bit off..
Thank you!
Gordon,
Based on the most popular "incorrect" answer, I'd say it was more than just you interpreting the question a different way. If the question author meant "multiple servers connecting to the same OLEDB connection once the package has started", it should have been worded so. Otherwise, package configurations are the natural way to change OLEDB connections at runtime.
Vince

Vince,

You are absolutly correct. Package Configurations are the natural way to change OLEDB connections at runtime. However the question was

In SQL 2005 Integration Services can an OLE DB connection be dynamicaly changed during execution.

I appologize if it was confusing. I did my best to word it clearly without giving away the answer completly.

I spent quite a bit of time making sure I had the definition of "Run Time" vs "Execution Time" correct.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 10

Steve Jones - SSC Editor

SSC Guru

Points: 736162

January 29, 2008 at 7:57 am

#772865

Sorry, bad question. Everyone gets points.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 11

Ah...then that splains..

Correct answers: 94% (803)
Incorrect answers: 6% (52)
Total attempts: 855
--------------------------------------------------------------------------------
No - In SSIS connections can not be dynamically updated. 6%
--------------------------------------------------------------------------------
No - In SSIS connections can only be updated at run time using package configurations. 10%
--------------------------------------------------------------------------------
Yes - In SSIS connections can be dynamically updated during execution using package configurations. 46%
--------------------------------------------------------------------------------
Yes - In SSIS connections can be dynamically updated during execution using the DTS.Connections collection. 38%
Total scores and standings

Thanks Steve!

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Lonely Rogue SSCrazy Points: 2579 More actions · Answer 12

Hi all,

Can some one give me solution for my problem...

Problem:

I have few SQL servers.. I need to design a DTS package that connects to all these servers and fetch the data ( eg query : select count(*) from master.sys.databases).

My approach :

I create a table with all the server names stored in it. Now, My DTS package shud connect to each server in the table and execute the query.. But for my the hurdle is ..how to change the ConnectionString dynamically..( i mean how to connect to all the servers one-by-one fashion)..

Any help/suggestion is appreciated.. can also suggest another approach also..

[font="Comic Sans MS"] Thanks in advance[/font]

[font="Comic Sans MS"]Vampire[/font]

--In 'thoughts'...
Lonely Rogue

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 13

I've actually had alot of success with 2 different methods.

1st and probably easiest:

Create 2 packages. The first one called "Cycle Servers" or something like it. All this one does is get the list of servers and loop through them putting the value into a variable.

I do this by creating an object variable and use an sql task to populate it as my result set. Then I use a For Each Loop container with the enumerator a Foreach ADO Enumerator, then my ADO object source variable is the variable (du 🙂 ).

Inside the loop I call my 2nd package.

Package 2: This one "does the work".

Here I have a Package Configuration using the "Parent Package Variable" that changes the connection. I my case I'm just changing the server name but you could change other parts of the connection also.

Now version 2 .. easier to explain but honestly I don't use it as often

Create a script task that changes the dts.connections("connection name").connectionstring.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Dugi SSCoach Points: 17998 More actions · Answer 14

Oh no my choice was 3rd answer and it is wrong ... Steve are you going to add 1 point to my score!

:w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]