April 9, 2012 at 5:42 am
Hi,
I've got a BIDS project that has multiple packages getting fired off loading in hundreds of data files.
On day one of the month I want it to do a set of packages and all other days I have it doing other packages. So At the very beginning I have a simple data flow task that does a check using a conditional split, if it is day one I allow the task to continue and if it's not I cause a failure. Then from this I redirect the error so that it follows another path in the flow.
In BIDS this works fine but as soon as I put it into SQL Server agent it hits the error and falls over, no longer handling it as before.
Is there a simple way of doing a conditional split in the control flow or is there a reason why a failure is handled neat and tidily in BIDS but not once it has been moved to SQL Server agent?
This is driving me mad so any help would be much appreciated.
Thanks
Jim
April 10, 2012 at 12:48 am
I would check the day in the control task either with a script task or with an Execute SQL Task. Put the result in a boolean variable, for example @firstDay. Connect this task with the other two parts of the control flow using precedence constraints. Double click on the precedence contstraints and pick "expression and constraint". As constraint, take success. For the expression, simply use your variable @firstDay.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 10, 2012 at 4:30 am
Thanks for that. I ended up doing that but I'm still confused as to why failures don't result in being handled as soon as they go in the agent.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply