Getting SSIS to fail gracefully

Question

Getting SSIS to fail gracefully

spencepa

Ten Centuries

Points: 1042
More actions
September 22, 2009 at 4:13 am

#135166

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

spencepa Ten Centuries Points: 1042 More actions · Answer 2

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!:-)

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

Sounds like you got it working well - good stuff:cool:

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin