SSIS restartability methods
Hi guys
Quesiton: Has anyone built an SSIS feed that handles restartabilty at the previous run's point of failure?
I have an SSIS package I'm working that has 100+ data flow tasks, 50+ for salesforce.com to SQL staging tables, and then 50+ from those SQL staging tables to SQL destination tables. I'd like to add restartability to this package, meaning if the feed fails at 2am at step 42, then when it's re-run at 3am it starts with data pump 42 and not 1.
I'm currently table-logging both the execution as one row in table ETL_PACKAGE_LOG, and each of the 100+ steps as one row in ETL_PACKAGE_STEP_LOG, so I can create a SQL task to determine the previous step that the package failed.
I don't need help with the SQL Agent 'How to restart a package if it fails' part of this question, just the how to handle it in SSIS.
Rather then build from scratch I though I'd ask here.
Thanks in advance.
Jim
Quesiton: Has anyone built an SSIS feed that handles restartabilty at the previous run's point of failure?
I have an SSIS package I'm working that has 100+ data flow tasks, 50+ for salesforce.com to SQL staging tables, and then 50+ from those SQL staging tables to SQL destination tables. I'd like to add restartability to this package, meaning if the feed fails at 2am at step 42, then when it's re-run at 3am it starts with data pump 42 and not 1.
I'm currently table-logging both the execution as one row in table ETL_PACKAGE_LOG, and each of the 100+ steps as one row in ETL_PACKAGE_STEP_LOG, so I can create a SQL task to determine the previous step that the package failed.
I don't need help with the SQL Agent 'How to restart a package if it fails' part of this question, just the how to handle it in SSIS.
Rather then build from scratch I though I'd ask here.
Thanks in advance.
Jim
ASKER
Can you speak specifically to the question? I'm not real big on 'here's a bunch of links, you figure it out' comments.
And I'm currently using containers around every task, and writing begin_dt, end_dt, rows_source, rows_inserted, rows_updated, etc. to my ETL_PACKAGE_STEP_LOG table.
And I'm currently using containers around every task, and writing begin_dt, end_dt, rows_source, rows_inserted, rows_updated, etc. to my ETL_PACKAGE_STEP_LOG table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://msdn.microsoft.com/en-us/library/ms141261.aspx
"Precedence constraints link executables, containers, and tasks in packages in a control flow, and specify conditions that determine whether executables run. An executable can be a For Loop, Foreach Loop, or Sequence container; a task; or an event handler. Event handlers also use precedence constraints to link their executables into a control flow. "
For example:
http://www.phpring.com/sequence-container-in-ssis/
http://msdn.microsoft.com/en-us/library/ms139855.aspx
There are loop containers as well:
https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/