Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Dynamic connections in SSIS Expand / Collapse
Author
Message
Posted Sunday, January 27, 2008 10:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 3,467, Visits: 1,838
Comments posted to this topic are about the item Dynamic connections in SSIS

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
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
Post #448056
Posted Monday, January 28, 2008 6:04 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:23 AM
Points: 672, 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."




Post #448252
Posted Monday, January 28, 2008 6:19 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:32 AM
Points: 4,631, Visits: 1,905
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.



Post #448258
Posted Monday, January 28, 2008 6:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 14, 2013 10:34 PM
Points: 428, Visits: 334
I would have to agree that No. 3 is correct also.
Post #448274
Posted Monday, January 28, 2008 7:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 3,467, Visits: 1,838
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
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
Post #448290
Posted Monday, January 28, 2008 7:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:31 AM
Points: 491, Visits: 225
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
Post #448308
Posted Monday, January 28, 2008 7:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 3,467, Visits: 1,838
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
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
Post #448323
Posted Monday, January 28, 2008 8:02 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:23 AM
Points: 672, Visits: 187
Fair Enough!!

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

Thank you!



Post #448330
Posted Monday, January 28, 2008 10:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:34 PM
Points: 716, Visits: 468
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



Post #448466
Posted Monday, January 28, 2008 12:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 3,467, Visits: 1,838
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
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
Post #448514
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse