SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kenneth Fisher / Dynamic connections in SSIS / Latest Posts

RE: Dynamic connections in SSIS

Kenneth.Fisher — Mon, 05 Nov 2012 10:27:37 GMT

[quote][b]adam_pearson (11/5/2012)[/b][hr]Hi Kenneth, Thanks for the example however no joy with that either. Using either a table as the source or hard coding it into the FOREACH container doesn't appear to work. When it executes it runs without problems however only returns the results from the local machine. The servername changes to the ones in the list (using a message box) however when putting items into a table (using the @@servername for example) it's only the location machine being picked up.I've tried setting the 'delayValidation' setting on and off but still no joy. Tried connecting to different servers but that hasn't helped either.Any suggestions?Thanks,Adam[/quote]Just to confirm. You are using a FOR EACH loop, not a FOR loop. You have a variable mapped (under the variable mapping tab of the FOR EACH loop container) to store the output of the loop. You have an expression set up on the connection manager that you want to be changing and it uses the variable that you mapped in the FOR EACH loop. And last but not least is the code that you expect to have pointing to the new server inside the loop container?If all that is true then see if you can walk through the setup on paper and post it to me. I would go ahead and use a Foreach Item Enumerator until you get it working since it has less moving parts to go wrong. Once we have that working then we can work on the next part.

RE: Dynamic connections in SSIS

adam_pearson — Mon, 05 Nov 2012 10:15:53 GMT

Ignore that comment Kenneth! Reason for no results was due to SQL Express not listing anything in sys.master_files so failing the query (and one of the engineers dropping ther other test machine I was using off the domain)! Will look into getting the dynamic part of this working now.thanks again!Adam

RE: Dynamic connections in SSIS

adam_pearson — Mon, 05 Nov 2012 10:06:57 GMT

Hi Kenneth, Thanks for the example however no joy with that either. Using either a table as the source or hard coding it into the FOREACH container doesn't appear to work. When it executes it runs without problems however only returns the results from the local machine. The servername changes to the ones in the list (using a message box) however when putting items into a table (using the @@servername for example) it's only the location machine being picked up.I've tried setting the 'delayValidation' setting on and off but still no joy. Tried connecting to different servers but that hasn't helped either.Any suggestions?Thanks,Adam

RE: Dynamic connections in SSIS

Kenneth.Fisher — Wed, 31 Oct 2012 13:46:37 GMT

I would check and make sure your secondary package has a package configuration set up. It would need to be type "Parent Variable" and you can use it to change the servername (or database, or whatever) of your connection manager. These days I prefer to use expressions though. I've posted a quickie demo on my blog for you. Hope it helps.[url=http://sqlstudies.com/2012/10/31/looping-through-multiple-servers-in-ssis/]Looping through multiple servers in SSIS[/url]

RE: Dynamic connections in SSIS

adam_pearson — Wed, 31 Oct 2012 11:40:46 GMT

Hi Kenneth, I've tried the option of a foreach loop both with dataflows in the foreach loop and also with a second package however always seem to encounter the problem that it only queries the first result from the table (connection string stored here).I've added a message box and can see the connection text changing however the actual connection doesn't change.Am I missing something obvious?Thanks,Adam

RE: Dynamic connections in SSIS

vipin.narayane — Thu, 26 Aug 2010 03:40:27 GMT

Another approach on dynamic connections by Hari Sharma on http://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html#comment-form

RE: Dynamic connections in SSIS

skyline666 — Fri, 30 May 2008 04:09:49 GMT

So what happens if I answered 3 but got it wrong, do I get the point still?

RE: Dynamic connections in SSIS

Dugi — Fri, 02 May 2008 00:48:49 GMT

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

RE: Dynamic connections in SSIS

Kenneth.Fisher — Tue, 05 Feb 2008 08:39:39 GMT

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 oftenCreate a script task that changes the dts.connections("connection name").connectionstring.Kenneth

RE: Dynamic connections in SSIS

Lonely Rogue — Tue, 05 Feb 2008 08:17:58 GMT

Hi all,Can some one give me solution for my problem...[b]Problem:[/b]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). [b]My approach :[/b] 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"][b]Vampire[/b][/font][img]C:\Documents and Settings\v-magill\Desktop\v-shivkt\DON.gif[/img]

RE: Dynamic connections in SSIS

Tom Garth — Wed, 30 Jan 2008 07:46:47 GMT

Ah...then that splains..[quote]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[/quote]Thanks Steve!

RE: Dynamic connections in SSIS

Steve Jones - SSC Editor — Tue, 29 Jan 2008 07:57:59 GMT

Sorry, bad question. Everyone gets points.

RE: Dynamic connections in SSIS

Kenneth.Fisher — Mon, 28 Jan 2008 12:12:26 GMT

[quote][b]viacoboni (1/28/2008)[/b][hr][quote][b]Gordon Radley (1/28/2008)[/b][hr]Fair Enough!!Obviously my interpretation of the question was a bit off..Thank you![/quote]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[/quote]Vince,You are absolutly correct. Package Configurations are the natural way to change OLEDB connections at runtime. However the question was [quote]In SQL 2005 Integration Services can an OLE DB connection be dynamicaly changed during [b]execution[/b].[/quote]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

RE: Dynamic connections in SSIS

viacoboni — Mon, 28 Jan 2008 10:40:08 GMT

[quote][b]Gordon Radley (1/28/2008)[/b][hr]Fair Enough!!Obviously my interpretation of the question was a bit off..Thank you![/quote]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

RE: Dynamic connections in SSIS

Gordon Radley — Mon, 28 Jan 2008 08:02:31 GMT

Fair Enough!!Obviously my interpretation of the question was a bit off..Thank you!

RE: Dynamic connections in SSIS

Kenneth.Fisher — Mon, 28 Jan 2008 07:55:34 GMT

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.

RE: Dynamic connections in SSIS

Jeff Deluca — Mon, 28 Jan 2008 07:42:05 GMT

I'm not sure if this helps anyone but I found this article. I've never done it this way myself.[url]http://msdn2.microsoft.com/en-us/library/ms136093.aspx[/url]

RE: Dynamic connections in SSIS

Kenneth.Fisher — Mon, 28 Jan 2008 07:19:51 GMT

[quote][b]Gordon Radley (1/28/2008)[/b][hr]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."[/quote]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.

RE: Dynamic connections in SSIS

Jim Howell — Mon, 28 Jan 2008 06:47:26 GMT

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

RE: Dynamic connections in SSIS

craigpessano — Mon, 28 Jan 2008 06:19:07 GMT

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.

RE: Dynamic connections in SSIS

Gordon Radley — Mon, 28 Jan 2008 06:04:40 GMT

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."

Dynamic connections in SSIS

Kenneth.Fisher — Sun, 27 Jan 2008 10:19:49 GMT

Comments posted to this topic are about the item [B]Dynamic connections in SSIS[/B]