May 23, 2017 at 9:33 pm
I have a data flow task which basically select top 100K records and inserts into a table, i have put a RowCount variable to capture the rows being inserted. I want this data flow task to execute in a while loop so i used forloop container, i want the execution to stop when RowCount = 0, looks like i almost got it but when i try to execute the container i get below message:
"Failed to to lock variable for read/write access with error"
Has anyone else experienced this? Thanks
May 24, 2017 at 7:56 am
Just to be clear, are you trying to control the number of rows loaded when the data flow task executes?If you put the data flow task inside a for loop, that won't have any impact on how many rows are loaded for each loop - it only controls how many times the data flow task is executed. If you want to limit the number of rows loaded in the data flow, you'll likely need to use row numbering (as I described in this article) and filter out those rows with a row number higher than the number of rows you want to load.
I hope this helps!
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 24, 2017 at 9:05 am
Really i just want to quit the loop when there are no more rows to insert, right now in my t-sql i am using select top and insert into a table, after each run @rowcount gets reset with @@rowcount, as @rowcount = 0 it exits. I was trying to get something similar inside ssis. My thought was i could just throw my existing data flow task, use rowcount task to get rows processed so far, store in a variable and pass that variable in for loop logic, when rowcount=0 it should exit the loop.
Thanks
May 25, 2017 at 10:40 am
First off I'm leery of your 'select top' methodology since it seems to lend itself to uncertainty. But beyond that.
The row count variable should be defined to at least the container level if you're going to use it there. And since it starts with a 0 value you may need to assign it a default value so it won't immediately exit the loop.
The value will be reset on each run so that's not an issue.
I think you can do it with some tweaking.
May 25, 2017 at 12:10 pm
Why are you even using a loop? Why don't you do it in a single execution? What changes?
May 25, 2017 at 3:31 pm
I have this @[User::Rows] == 0 in my evalcondition, it is only looping once though? The row count is being populated
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply