ForEachLoop task not behaving as expected

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 > script2 > script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Do you have the tasks hooked together by precedence constraints? (The green arrows?)|||

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

ForEachLoop Container and Variables

Hi Guys

I am trying to do the following and am quite new to SSIS.

I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.

I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.

I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.

I have then created another SQL task insert the Foreach which will perform the Update/Insert.

obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.

having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.

Which of the properties from the drop down do i need to use to map the variables against?

Any help would be massively appreciated asI am tearing my hair out!

Thanks

Scott

Hi Scott,

We're all still learning SSIS.

It sounds like you're most of the way there.

There are a couple ways to approach this solution. The simplest way, from what I understand from your post, is to use placeholders and parameters in your Update/Insert statements. If you already have the Code and Supplier variables defined, you could perform an insert using an Execute SQL Task with something similar to the following code:

Code Snippet

INSERT INTO Vendors

(Code, Supplier)

VALUES(?, ?)

You could then supply Parameters:

Code Snippet

VariableName Direction DataType ParameterName ParameterSize

User::Code Input Int 0 -1

User::Supplier Input VarChar 1 -1

This would substitute the question marks in the SQL Statement property with the values contained in your variables.

Hope this helps,

Andy

|||

Scott,

Any special reason for not using a dataflow with a lookup transform to detect if the rows exists(update) or not (insert). That is by far a pretty common practice in these scenarios.

|||

Hi Rafael

Still new to this (and database stuff as a whole) and am going on someone elses advice!

I have looked at your suggestion and have got as far as the following:

OLEDB Source with a SQL select statement to return the data required

Look Up transform to look up the 2 columns from the Select against the destination table

After that I am a bit lost. I guess i have to add a OLEDB destination but do I do it to a table or a SQL Command?

thanks again

Scott

|||

I think you are on the right track. I would add an OLE DB Destination against the destination table.

Keep in mind you have to tweak the lookup to 'redirect' errors. Lookup will treat the no matches as errors; hence will be send to the error output of the component (red arrow). Then you have to connect the error output of the Lup to the input of the destination.

Now the updates; every row going to the green output of the L.up is an existing/to-updated row. Here you have 2 options; use an OLE DB Commnad to update the row in the destination table; or send those rows to an estiging table (yes a seconf OLE DB Destination) and then back in control flow use an Execute SQl task to do a 1 time update. The advantage of the second method is performance. the Update runs 1 time updating all the required rows. The First one will perform an update for every row passing trhough; wich depending on the volume of data can be performance killer; the good thing is that you don't need a second table.

This thread has some examples

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

ForEachLoop Container - How to Force Next Iteration -

How can I force a Next Iteration in a ForEach Loop container?

I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).

If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.

Is it possible to force "Next Interation' on the parent container?

Thanks - Covi

Not quite sure what you mean. In what circumstances do you want to 'force teh next iteration'?

-Jamie

sql

ForEachLoop and Object-Variable

Hi there!

I want to use a ForEachLoop. I've an object variable what i fill before going into the ForEachLoop. It contains 4 columns and in my testscenario it has two rows. In the ForEachLoop i want to set the current row values to 4 package variables (within package scope).

So i set the Enumerater as "Foreach-ADO-Enumerator", the Ado-source-variable is my objectvariable (what contains the recordset), and the enumerator-configuration i set to "rows in all tables" ("rows in the first table" works with equal result).

The variable-mapping looks like that:

Mypackvar1 - Index 0

Mypackvar2 - Index 1

Mypackvar3 - Index 2

Mypackvar4 - Index 3

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Am i on the right way? Is it great bullshit what i am doing?

Thanks for any suggestion,

Torsten

Sounds like you're on the right track. What I do is create an ExecuteSQL task with the result set set to "Full result set". In the Result Set page I click Add, put 0 for the result name and pick an Object variable to put the result in.

In the For Each loop I make the collection a "Foreach ADO.Net Schema rowset enumerator".
In variable mappings I pick a variable with the same type as the column and put in the appropriate offset ( 0 through fieldcount-1).

It sounds like you're doing that or something very close. I'd double check the variable you're assigning to is the same type as the resultset column.

|||

Torsten_Katthoefer wrote:

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Have you stepped through with the debugger to make sure you're getting back the values you expect? Are you calling a stored procedure, or just executing SQL? How are you populating the recordset?

|||

Hmm, it works - a little bit...

One problem has been the datatype - in the db, the column is bigint, and the conversion to DTI8 makes some trouble, so i decided to use a an object as datatype (package scope), and first in the for-each-loop i started a script task like that (CRQ_ID is the variable with type DTI8, and CRQ_OBJ is the result from my query to set the enumerations):

Dim Message As String

Dts.Variables("v_CRQ_ID").Value = CType(Dts.Variables("v_CRQ_OBJ").Value, Int64)

Message = CStr(Dts.Variables("v_CRQ_OBJ").Value) + "-" + CStr(Dts.Variables("v_CRQ_ID").Value) + "-" + CStr(Dts.Variables("v_LGE").Value) + "-" + CStr(Dts.Variables("v_DDS").Value) + "-" + CStr(Dts.Variables("v_FIS_PERIODE").Value)

MsgBox(Message)

I get the message boxes a view times, and everytime CRQ_OBJ is equal CRQ_ID. Seems it works fine.

BUT: The next task is a sql task witch updates a few rows with the CRQ_ID as input parameter. But it doesn't matter on the new values. Could it be, that i've to use another way to set the package variable?