CODEWITHSUNDEEP
sql-serverssisvariable-assignmentforeach-loop-container
SomekindaRazzmatazz
SomekindaRazzmatazz

Reputation: 71

How to iterate SQL table values into SSIS variables within a Foreach Loop container?

I'm trying to create an SSIS package that will loop through a series of SQL expressions using variables. The variables to be set in SSIS would come from a SQL table, something similar to below.

Var1 Var2 Var3 Var4
NOV 2022 ABC 1
DEC 2022 EFG 2
JAN 2023 HIJ 1

I know that to incorporate SSIS variables into SQL statements I have to create an Execute SQL Task and set an expression for the SQLStatementSource that uses designated SSIS variables. What I'm less certain of is how to iterate through the table variables while within a ForEach loop.

An Example of what the Executed SQL task should look like would be something like...

SELECT [MONTH], [YEAR], [Code], [Level], *
FROM TableSource
WHERE [MONTH] like '[User::Var1]'
AND [YEAR] like '[User::Var2]'
AND [Code] like '[User::Var3]'
AND [Level] like '[User::Var4]'

For each row within the variable table selected originally, the variables within the statement should use all designated values across that row. Then, after performing the statement, it should loop back and continue the container using the next row's values for the variables and complete the same statement until the variable table has been iterated to completion.

Upvotes: 0

Views: 1155

Answers (2)

KeithL
KeithL

Reputation: 5594

  1. Create a variable of type object.

  2. Have a dataflow load your variable records into that object using a record set (maybe called dataset) destination object

  3. And then in the foreach, iterate through that object with your enumerator being an ADO object.

  4. Map the iteration to your columns.

Upvotes: 1

T N
T N

Reputation: 10204

A "where matches any of X" condition can often be coded up as "where exists(X where X matches)".

Try:

SELECT [MONTH], [YEAR], [Code], [Level], *
FROM TableSource T
WHERE EXISTS (
    SELECT *
    FROM Variables V
    WHERE T.MONTH = V.Var1
    AND T.YEAR = V.Var2
    AND T.Code = V.Var3
    AND T.Level = V.Var4
)

A join may be used for a similar effect, which might actually allow for a more efficient use of indexes.

SELECT T.*  -- (Apparent dup column references omitted)
FROM Variables V
JOIN TableSource T
    ON T.MONTH = V.Var1
    AND T.YEAR = V.Var2
    AND T.Code = V.Var3
    AND T.Level = V.Var4

Add a DISTINCT if there is a possibility of a TableSource row matching more than one criteria row.

If you really want a loop with each executing a separate select, you can use a CURSOR LOOP.

DECLARE @Var1 VARCHAR(10) -- Or whatever type is appropriate
DECLARE @Var2 INT         -- Or whatever type is appropriate
DECLARE @Var3 VARCHAR(10) -- Or whatever type is appropriate
DECLARE @Var4 INT         -- Or whatever type is appropriate

DEFINE VariablesCursor CURSOR FAST_FORWARD FOR
    SELECT Var1, Var2, Var3, Var4
    FROM Variables
OPEN VariablesCursor

FETCH NEXT FROM VariablesCursor INTO @Var1, @Var2, @Var3, @Var4
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT [MONTH], [YEAR], [Code], [Level], *
    FROM TableSource
    WHERE [MONTH] like @Var1
    AND [YEAR] like @Var2
    AND [Code] like @Var3
    AND [Level] like @Var4

    FETCH NEXT FROM VariablesCursor INTO @Var1, @Var2, @Var3, @Var4
END

CLOSE VariablesCursor 
DEALLOCATE VariablesCursor

Upvotes: 0

Related Questions