Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rowcount incorrect in Execute SQL Task


Rowcount incorrect in Execute SQL Task

Author
Message
donaldrmudge
donaldrmudge
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
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
donaldrmudge
donaldrmudge
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
cafescott
cafescott
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 733
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search