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