SSIS for each loop

Question

SSIS for each loop

SQLAssAS

SSCertifiable

Points: 7246
More actions
September 10, 2014 at 7:51 am

#290509

I have just set up an SSIS package similar to this
http://sql-bi-dev.blogspot.co.uk/2010/07/dynamic-database-connection-using-ssis.html
I have 5 connections in a SQL table which i want to loop through, set each string to be a connection string in the package (variable ConnString) and run a select to pull back information and store in a central table.
The package runs successfully and connects to 5 instances, but it connects to the same instance each time, my local one which is set as the value for variable used for the connection string.
How is the Connection string supposed to get passed from the @SourceList variable to the @Connstring variable? Its clear what the problem is, as the results of my query as step one which pulls back all 5 strings just isnt getting populated from @SourceList into @Connstring one by one. Any ideas??
SSIS package =
1) SQL Task - Select connection strings from SQLTable and store results in @sourcelist
2) for each loop - ADO Enumerator, rows in first table @Sourcelist
3) data flow task within the loop - OLE DB Source, dynamic connection as connection manager, running the query i want from a variable, with the destination set to my central database.

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

In the ForEach Loop properties, have you mapped the ConnString column of your @Sourcelist result set to your @Connstring variable. Is the @Connstring variable used to set the value of the Connection String property of your connection manager?

John

HowardW One Orange Chip Points: 29109 More actions · Answer 2

I think there's a step missing from that tutorial. You need to go into the Variable Mapping tab in the ForEach loop container and map the connection string variable to Index 0 of the collection.

Example shown here:

http://www.codeproject.com/Articles/14341/Using-the-Foreach-ADO-Enumerator-in-SSIS

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 3

Thanks! I knew i was missing something - now to complicate things further 😀