Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rowcount incorrect in Execute SQL Task Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 8:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:26 AM
Points: 2, Visits: 5
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?
Post #1442345
Posted Monday, April 15, 2013 9:57 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
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.

http://beyondrelational.com/modules/2/blogs/101/posts/13465/ssis-104-when-row-count-transformation-is-invaluable.aspx
Post #1442384
Posted Monday, April 15, 2013 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:26 AM
Points: 2, Visits: 5
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?
Post #1442410
Posted Tuesday, April 16, 2013 2:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:24 PM
Points: 146, Visits: 643
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.



Post #1443010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse