Foreach Loop Container. Can I assign each row to a different variable?

Question

Foreach Loop Container. Can I assign each row to a different variable?

JMI

SSChampion

Points: 11831
More actions
September 2, 2008 at 12:31 pm

#123336

I get 4 rows in my Full Result Set Object variable. Is there any way to assign each row to a different variable?
First I thought I can include some logic inside "Foreach Loop Container"
bit that seems to be not the case...
Is there any way to know which iteration you are doing?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You could use a ForEach and a script component. In the script component, you could include the logic to determine which variable to put each row into.

It may be more efficient to not return a full result set. If you know you are going to get 4 rows, why not use 4 Execute SQL Tasks instead of one and have each one of the tasks return one of the 4 expected rows? Then, you can use a single row result set and directly put the data into each variable. Bundle it all together with a sequence container and you are done.

JMI SSChampion Points: 11831 More actions · Answer 2

I thought instead of 4 "Execute SQL" tasks

I can just have 2:

"Execute SQL" and 1 Loop

I need to learn how to work with Loop task.

Mukti SSCrazy Points: 2826 More actions · Answer 3

Usefull links for working with Foreach container with Ado enumerator

http://www.sqlservercentral.com/articles/SSIS/64014/[/url]

and

http://www.codeproject.com/KB/database/foreachadossis.aspx

JMI SSChampion Points: 11831 More actions · Answer 4

Thanks SSC Rookie,

http://www.codeproject.com/KB/database/foreachadossis.aspx

is a very good example.

It gives you an idea how "Foreach Loop" task can be used.

But I still don't know how to loop through the result set

and make decisions.

A question.

Can I copy the variables from one package to another?

If I have like 10-15 variables in my first package and I want exactly the same variables in my second package how can I copy them?

JMI SSChampion Points: 11831 More actions · Answer 5

Here is my solution to the problem.

I create varCounter variable equals 1.

I have "Foreach Loop Container"

and map Result Set to "var_IterationValue" variable.

Inside "Foreach Loop" I have Script Task:

Public Class ScriptMain
Public Sub Main()
Dim var_IterationValue As String = Dts.Variables("var_IterationValue").Value.ToString
If CInt(Dts.Variables("varCounter").Value) = 1 Then
Dts.Variables("var_path1").Value = var_IterationValue
ElseIf CInt(Dts.Variables("varCounter").Value) = 2 Then
Dts.Variables("var_path2").Value = var_IterationValue
ElseIf CInt(Dts.Variables("varCounter").Value) = 3 Then
Dts.Variables("var_path3").Value = var_IterationValue
ElseIf CInt(Dts.Variables("varCounter").Value) = 4 Then
Dts.Variables("var_path4").Value = var_IterationValue
End If
Dts.Variables("varCounter").Value = CInt(Dts.Variables("varCounter").Value) + 1
If CInt(Dts.Variables("varCounter").Value) > 4 Then
MsgBox(Dts.Variables("var_path1").Value.ToString + vbCrLf + Dts.Variables("var_path2").Value.ToString + vbCrLf + Dts.Variables("var_path3").Value.ToString + vbCrLf + Dts.Variables("var_path4").Value.ToString)
End If
Dts.TaskResult = Dts.Results.Success
End Sub

(Of course you need to return the result set in an expected order)

So instead of 4 "Execute SQL" tasks

I just have 1 "Execute SQL" that returns a recordset

and 1 "Foreach Loop" where I assign my values.