0

Question: I have pertaining to SQL and SSIS :

How do I make an SSIS package work according to a logical flow that I mapped out for it?

Say there are six steps, I have the logic for how they should all run.

So for example, if A fails, do b, regardless of failure of b, do C, etc. But it is becoming a huge pain in the back trying to implement this in SSIS.

The conventional precedence constraints are too simple and 'linear' for this sort of thing. So, I thought maybe I could create a separate table that stores the values of 'flags' that I will set to 1 or 0 and these flags will correspond to the outcome of each step.

I will keep two sets of 6 values in a table, one for last run of my package and one for current run, then make comparisons to ensure consistency of data across multiple tables in multiple runs (accounting for any failure of any step this way) but I have no clue how to implement this right now.

3

This actually is pretty easy to do within SSIS. Taking your situation, I have 3 dataflows named A, B and C.

A B C Dataflows

You can change whether it is on Success, Failure or Completion by right clicking on the lines and setting that value

Right Click

I believe this gets you what you are looking for, at least for the scenario you have presented here.

| improve this answer | |
  • Thank you for your response mskinner. Is it also possible that I can base the progression of the flow on values stored inside an SQL table? I know it'll need to remember and update the values as it goes, and then also take actions based on values stored inside this table. Is that possible? So, say, A completed, I wanted to make an update to a flag in a table and take action for next step based on that. I will give you the exact scenario for A as an example: If in the lastrun, A didn't succeed or if A and B and C succeeded, then do A, or else go to D (because B and C are linked to success of A) – Ravi Sep 9 '15 at 16:06
  • @Jaywant - you can definitely create flags and have the process be considered a success or a failure based on those flags. You do that by clicking "Edit" from my second picture within the answer and from there you are given the opportunity to evaluate an expression and set the value of Success, Failure or Completion based on that evaluation expression. If you need it to be that complicated that is certainly possible. – mskinner Sep 9 '15 at 16:22
  • But I need to know the last run results of the entire workflow and execute only select steps this time based on last time's failures. I want to be able to hold processes that ran successfully last time and only run steps that failed. That is what's making this complicated to implement. – Ravi Sep 9 '15 at 16:31
  • Maybe if I could reference SSIS job steps from inside a stored procedure and run particular steps in a package from it, I could just have one stored procedure handle all the logic for the flow of things and also account for failures in previous run. – Ravi Sep 9 '15 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.