SSIS Using Foreach Container in certain way

Question

I am trying to use a Foreach Loop Container in SSIS to loop through a small set of rows and set the column 'id' to a variable.

I have the following variables:

• @startNumber = 5
• @maxNumber = 10

Here's how my loop would look in traditional syntax:

for (int i = @startNumber; i > @maxNumber ; i++)

This is the TSQL code I want to run to update my rows:

UPDATE myTable SET id =  i

1. Would I use a Foreach ADO Enumeration?
2. What would be my source variable?
3. How would I apply the logic above?
4. Would I include an Execute TSQL Statment for my update?

Any help is greatly appreciated.

Answer 1

I created a simple control flow to show you what I think you're after

I start with an Execute SQL Task to generate a basic table. You would probably have logic here instead to determine the starting number and/or the terminal value

if not exists
(
SELECT * FROM sys.tables AS T WHERE T.name = 'FLC'
)
BEGIN
    CREATE TABLE dbo.FLC
    (
        currentID int
    );
END

3 variables, scoped to package level

I configure the For Loop (not the foreach as your question has tagged) much as one would a classic for loop. Here I'm assigning a value to my SSIS Variable @currentNumber

Within my Execute SQL Task (inside the for loop) I am using @currentNumber as a parameter to my task.

INSERT INTO
    dbo.FLC
(
    currentID
)
-- OLE DB & ODBC connections use ? for ordinal parameter
-- ADO.NET uses named parameters like @foo
SELECT ? AS currentID;