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 and Data Flow

In my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.

Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.

This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?

Thanks,
Gary

We need to get the column values for each record in the "ForEach ADO Enumerator" in some variable.
Then for the tasks inside the ForEach container - we can use those variables.

Similar example is @. http://www.codeproject.com/useritems/foreachadossis.asp

Thanks,
Loonysan

sql

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.

ForEach Config validation errors

I have a Config file for a Data Flow that is utilizing a Configuration file. I have specified in the Configuration file to capture the Directory and FileSpec for the ForEach Enumeration.

When I open the package, I get the following error:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

Warning 2 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[FileSpec]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

I have verified that these two entries are indeed in the Config file and they do point to a valid location for the Directory and File Specification. The package runs fine.

Thanks.

More information:

When I am selecting these two fields during the Configuration file wizard, the final screen that shows the summary of activity displays the following info under the Properties section:

Properties:
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]
\Package.Connections[LoggingOutput].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSS].Properties[ServerName]
\Package.Connections[Corp1Staging].Properties[ServerName]

The FileSpec and Directory properties are under the ForEach.EnumeratorProperties section.

Is there a known issue pertaining to this?

|||What build are you running?|||

KirkHaselden wrote:

What build are you running?

June CTP|||UPDATE:

We deployed the dtsx to the file system on the server, made changes to the FileSpec parameter in the config file and deployed it, then executed the package pointing to the Config file.

The package still looked for the file as specified in the dtsx XML (package), regardless of what was specified in the config.

To confirm, we renamed the input file to match the FileSpec parameter in the package and it ran successfully, finding and processing the file.

The ForEach parsing for the Enumeration objects appears to have a bug. Can someone let me know if this is a known bug and if it is going to be fixed for the next CTP release?

Thanks.|||

We attempted to recreate the Config file uinder the September CTP and are having the same problems. The Config wizard

Here is what is displayed at the bottom of the dtsConfig file:

File name:
\\MSSQLYukon\etlconfig\ISOTypeLoss.dtsConfig

Properties:
\Package.Connections[ISOTypeLossLog].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSSStaging].Properties[InitialCatalog]
\Package.Connections[DSSStaging].Properties[ConnectionString]
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]

We still get the error in the Error Window:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

|||You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?|||

Cim Ryan wrote:

You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?

Absolutely. The errors I have highlighted above were copied and pasted from the confirmation screen of completing the Package Configuration Wizard and BEFORE the file is written.

The wizard itself does not recognize the setting before the file is written.

This is a HUGE issue if we are to deploy Yukon! |||

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

|||

Cim Ryan wrote:

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

Where in the Wizard should I be selecting the Directory and FileSpec properties? I select them under the <PackageName>/Executables/<ForEachContainerName>/Executables/EnumeratorProperties entry. In there I select "Directory" and "FileSpec".

I tried to post the XML but the Forum logged an error. Can I send the XML to you via email or post an attachment somewhere?|||Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.|||By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K|||

KirkHaselden wrote:

By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K

Kirk:

The problem is that we need to change settings in the config file to migrate the packages through Model, QA and to Production. As I posted above, I can change the settings to point to a different Directory but because the package cannot configure the setting it always uses the setting that was set when the package was designed, which is the Test directory. The only way to change this is to open the package, change the attributes during design and save them. But then it points to the Production directory, etc.

Changing the SuppressconfiurationWarnings setting will indeed hide the warning but it will not change the setting at execution time - which is the reason for having these properties in the config file in the first place.|||

Cim Ryan wrote:

Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.

Done. As stated, I am running September CTP.|||

I wasn't able to reproduce this failure, and Raul can no longer do so, either.

ForEach Config validation errors

I have a Config file for a Data Flow that is utilizing a Configuration file. I have specified in the Configuration file to capture the Directory and FileSpec for the ForEach Enumeration.

When I open the package, I get the following error:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

Warning 2 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[FileSpec]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

I have verified that these two entries are indeed in the Config file and they do point to a valid location for the Directory and File Specification. The package runs fine.

Thanks.

More information:

When I am selecting these two fields during the Configuration file wizard, the final screen that shows the summary of activity displays the following info under the Properties section:

Properties:
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]
\Package.Connections[LoggingOutput].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSS].Properties[ServerName]
\Package.Connections[Corp1Staging].Properties[ServerName]

The FileSpec and Directory properties are under the ForEach.EnumeratorProperties section.

Is there a known issue pertaining to this?

|||What build are you running?|||

KirkHaselden wrote:

What build are you running?

June CTP|||UPDATE:

We deployed the dtsx to the file system on the server, made changes to the FileSpec parameter in the config file and deployed it, then executed the package pointing to the Config file.

The package still looked for the file as specified in the dtsx XML (package), regardless of what was specified in the config.

To confirm, we renamed the input file to match the FileSpec parameter in the package and it ran successfully, finding and processing the file.

The ForEach parsing for the Enumeration objects appears to have a bug. Can someone let me know if this is a known bug and if it is going to be fixed for the next CTP release?

Thanks.|||

We attempted to recreate the Config file uinder the September CTP and are having the same problems. The Config wizard

Here is what is displayed at the bottom of the dtsConfig file:

File name:
\\MSSQLYukon\etlconfig\ISOTypeLoss.dtsConfig

Properties:
\Package.Connections[ISOTypeLossLog].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSSStaging].Properties[InitialCatalog]
\Package.Connections[DSSStaging].Properties[ConnectionString]
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]

We still get the error in the Error Window:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

|||You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?|||

Cim Ryan wrote:

You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?

Absolutely. The errors I have highlighted above were copied and pasted from the confirmation screen of completing the Package Configuration Wizard and BEFORE the file is written.

The wizard itself does not recognize the setting before the file is written.

This is a HUGE issue if we are to deploy Yukon! |||

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

|||

Cim Ryan wrote:

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

Where in the Wizard should I be selecting the Directory and FileSpec properties? I select them under the <PackageName>/Executables/<ForEachContainerName>/Executables/EnumeratorProperties entry. In there I select "Directory" and "FileSpec".

I tried to post the XML but the Forum logged an error. Can I send the XML to you via email or post an attachment somewhere?|||Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.|||By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K|||

KirkHaselden wrote:

By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K

Kirk:

The problem is that we need to change settings in the config file to migrate the packages through Model, QA and to Production. As I posted above, I can change the settings to point to a different Directory but because the package cannot configure the setting it always uses the setting that was set when the package was designed, which is the Test directory. The only way to change this is to open the package, change the attributes during design and save them. But then it points to the Production directory, etc.

Changing the SuppressconfiurationWarnings setting will indeed hide the warning but it will not change the setting at execution time - which is the reason for having these properties in the config file in the first place.|||

Cim Ryan wrote:

Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.

Done. As stated, I am running September CTP.|||

I wasn't able to reproduce this failure, and Raul can no longer do so, either.

Force a commit

Hi,

My data flow has several transformations:

1. Search an employee, if the employee already exists, update it, otherwise insert it.

2. Once the new employee is created, i have to get its id (with another search transformation )to update another table with it. This id is an autonumeric , thats the reason i have to get it once the record is inserted.

At this momment this second search transformation to get the assigned id for the new reacord doesnt find any employee... i suppose its because these new data is not commited in the database....

the question is, Its possible to force a commit?

Thanks!

try to use T-SQL function IDENT_CURRENT

"IDENT_CURRENT returns the value generated for a specific table in any session and any scope."(from Books Online)

|||

thanks for your answer ggciubuc,

I have seen the IDENT_CURRENT documentation, and i think it could work if the flow process rows isolated ( ie. using a for each bucle), but in my case, the output of the oledb command transformation is a bunch of rows, so , i think that using this function i get only the last id generated... am i wrong?

Any other suggestion?

Thanks

|||

After your last post i suppose there are many clients that run your package, so there are many ID's.

But let's think at these ID's; you are got in a variable IDENT_CURRENT of time t0 an unique value of ID

if someone run the some package, the variable get another value of IDENT_CURRENT of time t1, and t0<t1

this because I thing this running of package is a consecutive running.

Try this ideea.

|||

Another ideea is to use 2 Data Flow tasks

In first you update/insert employee then you have source-transform-destination, so is made a commit and in second

you update another tables. Link with a "Constraint precedence" arrow with value "succes"

|||

thanks ggciubuc,

Thats a good solution , the problem is that the time to reatrive data source is to large. If i split in two data flows, i have to get these data two times...

For Travis Lowdermilk

I dont know if anyone mentioned this, but I would like to have the ability to disable objects in a Data Flow Task

Travis,

I've split this into a seperate thread because it isn't relevant to the previous thread.

What is the scenario for wanting to do this? I don't understand why this would be required or how it would even be possible.

e.g. If you have a data-flow with 3 components, components 2 & 3 would not be able to execute if component 1 were disabled. The unit of execution in SSIS is a task, not a component.

-Jamie

|||

Jamie -

I apologize for posting in the wrong topic.

The reason I think it would be cool to have the ability to disable a particular object in a Data Flow Task is:

Scenario:

Let say I have a Multicast that splits the data to into two different directions:

Direction 1: goes to a JOIN and then eventually to an OLE DB DELETE Transform

Direction 2: goes through a Lookup transform and then to an OLE DB UPDATE or INSERT.

I may want to test just Direction 2 exclusively (maybe I know Direction 1 isn't going to work yet <maybe there was a change on the db side that makes this path invalid now>)

It would be nice to disable the path on the Direction 1 side and just focus on Direction 2.

Or maybe I want to just see if the LOOKUP (Direction 1 path) is redirecting the correct number of rows but I don't want to execute the INSERT OLE DB at the end of the path.

I have already created an extensive OLE DB INSERT statement that I don't want to have to recreate. Disabling it would be nice.

I am no expert at this sort of stuff, so it is quite possible that my sense of design is flawed and therefor I have to rely on deleting/cut and pasting items in the Data Flow Task to have them be ignored during debugging.

Hope that makes sense :-)

|||

Yeah that does kinda make sense. I guess that rather than disabling a component it'd be more accurate to say "stop the flow at this point".

I think its a valid request. You should request it at the feedback center.

In the meantime you can achieve the same thing by putting a data viewer on the path that you're not interested in. That'll effectively halt the flow.

-Jamie

|||

Thats a great idea.

Thanks!

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

Hi,

I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?

I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?

Please help me with the above problem as I am working on a demo.

Thank you!!

Here's how you do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx and there is a downloadable demo on there as well

The only difference is that in the example given here the recordset object variable is populated with an Execute SQL Task rather than the Recordset destination but thereafter the same applies.

You cannot use the DataReader destination by the way!

-Jamie

For each container - performance problem?

I have a for each container configured to iterate over files in a directory (around 2600 files). The for each container contains only one data flow task. The data flow task is very simple. Only 3 steps. Read from file using flat file source (ragged right), format column values (eg. converting YYYYMMDD into yyyy/mm/dd and TRIM spaces - 13 such conversions) using derived column transform and inserting them into a table (23 columns - mostly nvarchar, few numerics and few dates) using Ole Db Destination. The table has one non-unique clustered index on nvarchar(18) column. Each of the 2600 file has at an average 750000 rows.

It seems to me that memory deallocation doesn't happen after each iteration of the loop (I might be wrong). So after few iterations, sqlservr.exe is hogging all available memory (2GB in this case) whereas my dual processors are running at 3-6 % of capacity. As a result, to process less than 5 million rows, it is taking 6 hours.

I am surprised that for such a trivial operation, performance should degrade this much.

One thing that is suspicious is that the files are residing on a USB 2 external drive.
Also, the database files are on the same drive. But, still... why first few iterations zap through like missiles while the later ones seem to be competing with snails?

Any insight?

TIA,
NiteshDo you use transactions? If yes, I suspect the cause of the problem that all files are submitted as part of single transaction - so the Sql Server has to keep all the data before committing the transactions.

I would rather avoid transactions unless really needed and use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.|||Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie|||

Michael Entin SSIS wrote:

Do you use transactions? ...

No, I am not using Transactions.

Michael Entin SSIS wrote:

...use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh|||

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh|||

Nitesh Ambastha wrote:

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh

There's only one way to find out :)

I'll be brave and say it should be able to handle it. Remember, all it does is swap buffers in and out of memory - once the contents of a buffer is inserted into the target the buffer "disappears".

You may have to play with buffer sizes etc... to get optimal performance but that sounds like an interesting piece of work if you ask me (perhaps that's just me! )

Let us know how it goes anyway. You should definately try it.

-Jamie|||

Nitesh Ambastha wrote:

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh

Correct. SQL Server Destination can only be used when the target is local.

-Jamie