ForEach Trapping an Error and Continuing

I have a ForEach loop that processes a list of databases. Inside the loop I many steps, one of which is a sequence that contains two steps. Either of these steps may fail (they are attempting to start mirroring and could fail for any number of reasons). I would like to trap this error and ignore it so the For loop will continue, but still fail if other steps than this one fail. The only thing I've been able to do so far is to tell the whole loop to continue through some insane number of errors. Is there a way to identify or actually ignore the error? In the sequence I have have on completion and from the sequence to the next step (which checks if mirroring actually started) is running on completion.

Thanks.

I found a solution using SQL Server.

BEGIN TRY
ALTER DATABASE AdventureWorks SET Partner='http://TEST'
END TRY
BEGIN CATCH
END CATCH

This will prevent the error from being seen by SSIS. But for other errors this will not work (such as SELECT * FROM person.contacts) where contacts does not exist in the adventureworks database (person.contact does).

I'd still be interested in any feedback on ways to selectively trap and ignore errors and get the for loop to continue.

Larry

|||

For selectively ignoring an error, one approach I've used it to modify the MaximumErrorCount to greater than 1 (say 1 billion) on the task (or container) I want to selectively ignore errors on. Then, put an error handler on the task or container which basically sets a variable for fatal errors and use expression based precedence constraints rather than success/failure/completion precedence constraints.

Public Sub Main()

' Don't propagate error message up the chain, is this propagated

Dim errorMessage As String

Dts.Variables("Propagate").Value = False

'inspect error message

errorMessage = CType(Dts.Variables("ErrorDescription").Value, String)

If errorMessage.Contains("really bad error here") Then

Dts.Variables("FatalError").Value = True

End If

Dts.TaskResult = Dts.Results.Success

End Sub

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

ForEach Loop Container and First Task

Hi,

My Foreach Loop container has 10 different task inside. I want to execute the first task only one time. I have a variable with increases for each repition. How can I put precedence contstraint on the first task so that it should execute only first time and other task has to execute all the time.

Thanks

You can add a property expression on the task that will set the Disable property to True/False accordingly

Thanks,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

You can add a property expression on the task that will set the Disable property to True/False accordingly

Thanks,
Ovidiu Burlacu

Far be it from me to disagree with Ovidiu (who knows far more about SSIS then I ever will) but I think a better approach would be to make all of the tasks execute after an empty sequence container. You can then put an expression on the precedence constraint that goes between the sequence container and the task you only want to execute on the first iteration.

-Jamie

Foreach Loop - Value back to 0

Hi,

Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data.

Somewhere inside the Foreach Loop, you set an int variable MyInt to a value.

My question:

Is it possable when each iteration begins to set MyInt to 0?

Thanks in advance.

Is the foreach loop evaluating the same variable, or are you using the variable for something else?

Script tasks can assign values to variables.|||Can you set a value of a variable inside an expression?|||

MrHat wrote:

Can you set a value of a variable inside an expression?

No.|||But you could set it by running "SELECT ? = 0" inside an Execute SQL task at the start of your loop, and assigning that output parameter to your variable.

For/Foreach loop

Hi,

I'm looping through some query data and doing a Script Task check inside the loop on a datetime field.

If the datetime happens before 10 o'clock:

- Store the data row in Table1

If the datetime happens after 10 o'clock:

- Store the data row in Table2

How can I store the datarow inside the loop without inserting it into a database table?

I need to access the data in the next step after the loop. How can I do this?

Thank you very much!

Can you store the values in some variables?

|||

Thanks for the reply.

Yes, I could. But if I'm looping through 100 lines of query data, and I want to store 50 of them, wouldn′t it mean a whole lot of variables to store all the data?

Am I misunderstanding you perhaps?

|||If you have a single source connection for the data and you want to split the rows based on the value of a certain column, used a conditional split, as per previous post, and then write the rows to a raw file for later use.

Other option would be to generate the rows based on the conditions and then write them to variables, as per Jamie's option, and use them later.

Depending on the number of rows and different conditions you have, either option would work. Variable route may give performance / resource problems on large numbers though.|||

MrHat wrote:

Thanks for the reply.

Yes, I could. But if I'm looping through 100 lines of query data, and I want to store 50 of them, wouldn′t it mean a whole lot of variables to store all the data?

Yes it would. Is that a problem?

MrHat wrote:

Am I misunderstanding you perhaps?

I don't think so

-Jamie

for loop expression

I'm trying to run a data flow task inside of a for loop container.

First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.

So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.

I've set the following:
AssignExpression : @.counter = @.counter + @.inc
EvalExpression : @.counter < @.max
InitExpression : @.counter = 1

To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.

What is the trick? Once I get this, then I can set these variables to other variables.

Thanks in advance.
-Lori

Are you sure @.inc is 2 in the scope of the for loop? Maybe check that it is not zero in the msg box.

Mark

|||I think I was doing something stupid like that when all the variables were ints.

Now I've moved to the next step, which is all the variables need to be strings. This is because both the @.max and the initial @.counter will be set based on values I pull from the database. When getting these values they have to come out as strings, otherwise the query fails.

@.max is going to be set to the max id of a table in mysql.
@.counter (initially) will be set to the max id of a table in sql server.
The goal is to pull all rows from the mysql table to the sql server table between @.counter and @.max. I'm doing this in a forloop so as to lessen the number of rows that the job tries to pull from mysql at any given time.

Once again, I've stepped back and am just trying to get the looping to work with strings.
Here are my expressions:
Currently the looping is not working:
Assign: @.counter =(DT_WSTR,50)((DT_U18)(@.counter) + (DT_U18)@.inc))
When running in debug mode, the counter is not incrementing. I've check that @.inc is not 0, by having it displayed to me in a msgbox.

Having to manipulate everything as strings to ints to strings again is extremely frustrating.

Thanks for help,
-Lori|||

I'm not sure I understand why the variables need to be strings in the for loop container. YOu get the values once, before the ForLoop begins, correct? Could you cast them at that time, or make integer copies for the ForLoop to simplify the expressions?

Thanks
Mark

|||Hmmm. That was fancy. Wrote this big long response on how to achieve this, and the forum says "can't post for unknown reason", and I lost the whole response. Let's try again.

I used to work with DTS. It was extremely straightforward to get max id from a table, set it to a parameter and then use that parameter in the pull command. SSIS is not so straightfoward. It took me about 3 full days to figure how to do what I just mentioned in SSIS. Then luckily I wrote a howto for myself and colleagues because I couldn't recall how to do it a month later. What I learned in those three days was that when getting the max id from a table, you have to convert it to a string because that's the only way to not have data loss when using max(id).

For others, here's the trick from mysql and sql server.
mysql (using ADO.net/odbc provider): select cast(max(id) as char) as max from <table>
sql server(using OLE DB provider): select convert(nvarchar(20), max(id)) as max from <table>
When setting the result set:
mysql: ResultSetName: 0, Variable name: <whatever>
sqlServer: ResultSetName: max, variable name: <whatever2>

Then to create the sql command, you had to click on DataFlow Task, Expressions, and set up an Expression for the sqlcommand. We had to do this, so that we could use the max string. If it's an integer, you don't have to do this.
"select * from <table> where id > " + @.[User::max]

So after finally figuring all that out, I now try to create a forloop. Since I kept fighting with integers vs. strings, I thought maybe it's just best to have every variable be a string. I don't think that this is that crazy of an assumption after all the **** I had to deal with.

But it turns out that setting @.counter as a string just doesn't work. Either the looping doesn't work or the loop won't end. Depends on lucky you get. I finally figured this out though.
Create your @.counter and @.inc variables with the Package as the scope and as Int64.

Then do the following for the forloop expressions:
InitExpression: @.counter = (DT_I8)@.mssql_max
EvalExpression: @.counter < (DT_I8) @.max
AssignExpression: @.counter = @.counter + @.inc

Then for the sqlcommand in the dataflow task, do the following:
"select * from <table> where id > " + (DT_WSTR, 50) @.counter + " and id <= " + (DT_WSTR, 50) ( @.counter+ @.inc).
Convert @.counter to a string, and (@.counter + @.inc) to a string. This is working very well.

I hope this helps someone else out as there didn't seem to be any examples of this out there.
Good luck to everyone in SSIS land.|||

After fighting the same problem for several hours I found this post that finally helped me on the right track...

However for if you like me use the mysql-odbc connector the index to the parameter seems to be starting on 1.

mysql: ResultSetName: 0, Variable name: <whatever>

Should then needs be changed to:

mysql: ResultSetName: 1, Variable name: <whatever>

/Albert

for loop expression

I'm trying to run a data flow task inside of a for loop container.

First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.

So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.

I've set the following:
AssignExpression : @.counter = @.counter + @.inc
EvalExpression : @.counter < @.max
InitExpression : @.counter = 1

To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.

What is the trick? Once I get this, then I can set these variables to other variables.

Thanks in advance.
-Lori

Are you sure @.inc is 2 in the scope of the for loop? Maybe check that it is not zero in the msg box.

Mark

|||I think I was doing something stupid like that when all the variables were ints.

Now I've moved to the next step, which is all the variables need to be strings. This is because both the @.max and the initial @.counter will be set based on values I pull from the database. When getting these values they have to come out as strings, otherwise the query fails.

@.max is going to be set to the max id of a table in mysql.
@.counter (initially) will be set to the max id of a table in sql server.
The goal is to pull all rows from the mysql table to the sql server table between @.counter and @.max. I'm doing this in a forloop so as to lessen the number of rows that the job tries to pull from mysql at any given time.

Once again, I've stepped back and am just trying to get the looping to work with strings.
Here are my expressions:
Currently the looping is not working:
Assign: @.counter =(DT_WSTR,50)((DT_U18)(@.counter) + (DT_U18)@.inc))
When running in debug mode, the counter is not incrementing. I've check that @.inc is not 0, by having it displayed to me in a msgbox.

Having to manipulate everything as strings to ints to strings again is extremely frustrating.

Thanks for help,
-Lori|||

I'm not sure I understand why the variables need to be strings in the for loop container. YOu get the values once, before the ForLoop begins, correct? Could you cast them at that time, or make integer copies for the ForLoop to simplify the expressions?

Thanks
Mark

|||Hmmm. That was fancy. Wrote this big long response on how to achieve this, and the forum says "can't post for unknown reason", and I lost the whole response. Let's try again.

I used to work with DTS. It was extremely straightforward to get max id from a table, set it to a parameter and then use that parameter in the pull command. SSIS is not so straightfoward. It took me about 3 full days to figure how to do what I just mentioned in SSIS. Then luckily I wrote a howto for myself and colleagues because I couldn't recall how to do it a month later. What I learned in those three days was that when getting the max id from a table, you have to convert it to a string because that's the only way to not have data loss when using max(id).

For others, here's the trick from mysql and sql server.
mysql (using ADO.net/odbc provider): select cast(max(id) as char) as max from <table>
sql server(using OLE DB provider): select convert(nvarchar(20), max(id)) as max from <table>
When setting the result set:
mysql: ResultSetName: 0, Variable name: <whatever>
sqlServer: ResultSetName: max, variable name: <whatever2>

Then to create the sql command, you had to click on DataFlow Task, Expressions, and set up an Expression for the sqlcommand. We had to do this, so that we could use the max string. If it's an integer, you don't have to do this.
"select * from <table> where id > " + @.[User::max]

So after finally figuring all that out, I now try to create a forloop. Since I kept fighting with integers vs. strings, I thought maybe it's just best to have every variable be a string. I don't think that this is that crazy of an assumption after all the **** I had to deal with.

But it turns out that setting @.counter as a string just doesn't work. Either the looping doesn't work or the loop won't end. Depends on lucky you get. I finally figured this out though.
Create your @.counter and @.inc variables with the Package as the scope and as Int64.

Then do the following for the forloop expressions:
InitExpression: @.counter = (DT_I8)@.mssql_max
EvalExpression: @.counter < (DT_I8) @.max
AssignExpression: @.counter = @.counter + @.inc

Then for the sqlcommand in the dataflow task, do the following:
"select * from <table> where id > " + (DT_WSTR, 50) @.counter + " and id <= " + (DT_WSTR, 50) ( @.counter+ @.inc).
Convert @.counter to a string, and (@.counter + @.inc) to a string. This is working very well.

I hope this helps someone else out as there didn't seem to be any examples of this out there.
Good luck to everyone in SSIS land.|||

After fighting the same problem for several hours I found this post that finally helped me on the right track...

However for if you like me use the mysql-odbc connector the index to the parameter seems to be starting on 1.

mysql: ResultSetName: 0, Variable name: <whatever>

Should then needs be changed to:

mysql: ResultSetName: 1, Variable name: <whatever>

/Albert

For each Loop Container do nothing inside

Hi,

A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?

Your help is much welcome. Thanks in Advance.

Jo?o Cruz

Not enought nformation to tell really, but likely related to the code in the script task. Without knowing what the package looks like, what precedence contraints you have etc., it's really tough to know.

More information please...

|||

I have found the solution . Thanks very much for your time.