June 21, 2010 at 9:42 am
I swear every time i deal with SSIS its like having to go back to your ex-spouse or girl/boy friend who hates you and get them to help you with something. I've tried real hard to embrace SSIS but its like it wants you to hate it.
The problem this time is with getting SSIS to make calls asynchronously (one at a time) so that step X + 1 does not start until Step X has completed (regardless of whether Step X fails).
I have a SSIS 2005 package consisting of several Execute SQL Server Agent Task tasks and each one i set to execute upon completion of the prior. Little did I know at the time that SSIS interpreted the completion of an SQL Job not as it actually completing but as the call to it completing. So if the job actually takes 1 second to call and start but another 2 hours to complete, SSIS considers that job complete after that first second of time has passed and thats because the Job started and thats good enough for SSIS. UGGGHHHH!
Does anyone familiar with SSIS have any suggestions on how to get SSIS to recognize the dictionary definition of the word COMPLETE and therefore not execute the next task until the prior tasks has actually completed?
Thanks
Kindest Regards,
Just say No to Facebook!June 22, 2010 at 3:36 am
This is not the fault of SSIS. The completion precedence constraint checks the completion of the task (whether this is a SQL Server Agent task or another one). The task of the SQL Server Agent Task is just to call a job. If that task is finished, it is finished. You just can't expect to do more than it is meant to do.
SSIS is an ETL-tool, not a job management tool. I think stored procedures are easier to achieve your goal.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 23, 2010 at 9:10 am
This isn't really an SSIS problem it just so happens you noticed it using SSIS but it has been this way for as long as I can remember, going all the way back to at least SQL 6.0.
There are no methods (that I know of) that are provided out of the box that do what you want. When you use either the sp_start_job sproc or the SSIS component, it is just requesting a job start it is not waiting for completion of the job, only the request. You would have to write some code to watch the job status and return good/bad when it completes.
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply