SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic connections in SSIS


Dynamic connections in SSIS

Author
Message
Kenneth Fisher
Kenneth Fisher
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12367 Visits: 2121
Comments posted to this topic are about the item Dynamic connections in SSIS

Kenneth Fisher
I 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/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Gordon Radley
Gordon Radley
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 187
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
craigpessano
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5214 Visits: 2276
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
Jim Howell
Right there with Babe
Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)

Group: General Forum Members
Points: 760 Visits: 334
I would have to agree that No. 3 is correct also.
Kenneth Fisher
Kenneth Fisher
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12367 Visits: 2121
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 Fisher
I 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/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Jeff Deluca
Jeff Deluca
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 229
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
Kenneth Fisher
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12367 Visits: 2121
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 Fisher
I 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/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Gordon Radley
Gordon Radley
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 187
Fair Enough!!

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

Thank you!



vince.iacoboni@db.com
vince.iacoboni@db.com
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2691 Visits: 571
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
Kenneth Fisher
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12367 Visits: 2121
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 Fisher
I 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/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search