CODEWITHSUNDEEP
sqlsql-serversql-server-2008foreachssis
slevin37
slevin37

Reputation: 33

SSIS ForEach Loop with count

I'm looking to create a ForEach Loop in SSIS:

Essentially, I have a set of data with ID numbers that must be unique. When I assign the IDs, I have a check that will make sure these IDs don't already exist in a members table. I am using a count(*) of every record that has a match to determine this. If the count is >0, the records in the initial table are removed and re-inserted to assign it a new ID.

What I'd like to do is setup a loop to continue this process until that initial count is 0 (in case re-adding the record gives it an ID that is also existing in the members table.

I believe a ForEach Loop with Variable enumerator is the way to go here, but I'm not quite sure how to set this up using a count.

Upvotes: 1

Views: 2895

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

What you describe sounds more like an SSIS For Loop container, not a For Each Loop. The For Loop can test an evaluation expression to decide whether to run another iteration of the loop (e.g. @count != 0).

Here's the doco on the For Loop container:

https://msdn.microsoft.com/en-us/library/ms139956.aspx

The For Loop also provides for initialization and iteration expressions, but it sounds like you would not need these. The definition entry for your count variable can initialize it to a non-0 value.

Upvotes: 0

Related Questions