September 22, 2009 at 4:13 am
Hi all, i have a SSIS package (2008) that i want to schedule to run every 15 mins to move data from staging to live. The job calls a SP (execute sql task). The SP returns the ID of a dataset that needs to be imported into live, assigns it to a variable and passes it to a data flow task.
The problem is if there is no data to be imported - the SP returns NULL and the job fails, writing a line into the SQL logs. I cant use "row count" as i need to success/fail at the control flow , not data flow level
I would like it to do nothing if the SP returns NULL so how do i stop it failing?
cheers
September 22, 2009 at 5:39 am
Use an Execute SQL task (select count(*) from ...) to assign the count to a package variable (which you will have to create) at control flow level and then use precedence constraints to control whether your DF is called or not.
September 22, 2009 at 6:56 am
thanks for the pointer but cant do a "select count(*) from..." as the data comes from a SP - but you got me thinking
I changed the SP to have 2 parts seperated by a If statament - either a "select count(*) form tablex..." or a "Select data....from tablex..." A flag on the SP eg "exec mysp 0" runs the count or the data returning either a count or the data
I then put a new step at the beginning, to call the count, puts it into a variable, i then put a expression in the flow (@[User::count] != 0) to call the data flow step or (@[User::count] == 0) to do nothing
All green - Job done!:-)
September 22, 2009 at 8:39 am
Sounds like you got it working well - good stuff:cool:
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply