April 15, 2013 at 8:56 am
I think I'm running into some sort of table lock issue within my SSIS package. I have a Data Flow task that does a fast data load from one table to another, very simple. The next task is an Execute SQL Task that only pulls the count from the destination table and pushes it to a variable to be consumed later in the package. The problem I have is that it only returns "1" for the value when it's supposed to have 3. I run the exact same code in SSMS and get 3, but the package shows 1.
Will the table be locked and not show the correct row count without some sort of transactional commit or something along those lines?
April 15, 2013 at 9:57 am
I have had no problems using execute sql task to populate a variable. How do you have it configured?
Also in your data flow task you can instead place a Row Count Transformation between your source and destination items to give you a row count of items transferred.
April 15, 2013 at 10:48 am
After digging into a little deeper, I think I figured out the source of the problem. There are several tasks before the data flow task, and that task is on a conditional constraint with several others (based on what code is passed from the job, it will run a different branch). What I'm finding out is that it's running the count before it runs the bulk insert. The Execute SQL task is after the Data Flow task, so is this normal behavior?
April 16, 2013 at 2:24 pm
I have found that with conditional processing, a sequence container is pretty nifty. The sequence container produces a single result (i.e., success or fail) instead of each task having its own result, even if one of the tasks was supposed to be bypassed by a condition.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply