Solved

SSIS restartability methods

Posted on 2014-11-20
5
169 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:Jim Horn
  • 3
5 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 40455699
I did similar things by grouping tasks in "sequence containers" inside SSIS and use "Precedence Constraints" in workflow/decision making based on output.

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/
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 40455719
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.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 450 total points
ID: 40455746
You can use "expressions" on the "precedence constraints" based on the ETL_PACKAGE_STEP_LOG table to determine if a sequence container should be executed or not. So let's say your SSIS started from SQL Job failed at 2AM and you put a retry on the job step or leave it for next run at 3AM when your SSIS will get the "expression" value (Run or do Not Run) based on the log table from the previous execution of that particular container/step. Hope this makes sense.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 450 total points
ID: 40455757
"...if the feed fails..." - you could also use that log table (but this means more work indeed) to write a SQL query  (rather than default source table destination table SSIS data task) to transfer the data or not based on what was lastly logged for that particular task based on success/failure in the log.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 50 total points
ID: 40464339
Hi,

In that case you can use SSIS checkpoint file feature.

When your process fails. In next run It will start from the point where it was failed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
calculated column 12 72
2 Select Distinct 8 35
Permissions on Database 11 36
TSQL - IF ELSE? 3 24
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now