April 12, 2010 at 1:26 pm
Hi
I am new to SSIS.
How can i know the status of the last run of SSIS Packages ?
I am trying to run the SSIS package based on the last status of the SSIS package,
if the last run status is a success then it runs
certain tasks if not it runs other tasks.
How can i implement this in my SSIS package.
Thanks for the help in advance.
April 13, 2010 at 1:05 am
[font="Verdana"]
Go through the below articles to explore more on SSIS.
SQL Server Integration Services an Introduction - Part 1
http://www.sql-server-performance.com/articles/biz/SSIS_Introduction_Part1_p1.aspx
SQL Server Integration Services Features And Properties Part 1
http://www.sql-server-performance.com/articles/biz/SSIS_Features_And_Properties_Part1_p1.aspx
SSIS - An Inside View Part 1
http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_1_p1.aspx
Anyways, you need to add precedence for Success and Failure to the task in your case.
--Mahesh
[/font]
MH-09-AM-8694
April 13, 2010 at 2:48 am
You could execute your packages in a SQL Server job.
For each jobstep, you can specify what to do with success and failure. Point to a specific jobstep that updates a value in a table. Then use this value in your packages to control execution.
For example:
you have one jobstep. If it succeeds, go to jobstep 2, which sets a value in a table SSISJob (this table has for example a column with the name of the SSIS package and a column that indicates if the last run was succesful or not.).
If it fails, go to jobstep 3, which also updates the same table (but with the value Failure this time).
In the beginning of your package, read the variable with an Execute SQL Task and write the value to a variable stored in your SSIS. Then use precedence constraints with expressions to control the flow of your package.
This is the first solution I can think of, it's possible there are (better) alternatives.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2010 at 2:51 am
Another alternative (my brain takes some time to warm up :-D) is to set the value of the table I spoke of at the end of the package. Keep a variable in your package that keeps track of the success/failure of your tasks (you can set this variable in a script task, which is connected to your other tasks with a red arrow).
At the beginning of the package execution, overwrite the variable value with the one in the database. Just make sure it has an initial value, so your package doesn't crash on the first run.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2010 at 7:16 am
You can also create another package in SSIS and add an Execute Package task for each of your other packages, and then simply put a success precedence constraint between the first and second one. Package scheduling would be done on the master package, not the individual packages.
April 13, 2010 at 7:29 am
dbowlin (4/13/2010)
You can also create another package in SSIS and add an Execute Package task for each of your other packages, and then simply put a success precedence constraint between the first and second one. Package scheduling would be done on the master package, not the individual packages.
I think the original poster meant that he wants to know the execution result of the same package when it was run the previous time. I believe your set-up is for different packages.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2010 at 10:07 am
da-zero (4/13/2010)
dbowlin (4/13/2010)
You can also create another package in SSIS and add an Execute Package task for each of your other packages, and then simply put a success precedence constraint between the first and second one. Package scheduling would be done on the master package, not the individual packages.I think the original poster meant that he wants to know the execution result of the same package when it was run the previous time. I believe your set-up is for different packages.
Ahhh, now I get it. If I had to stick to SSIS I might consider creating a file that contained the success or failure status of the package when run last, then always read that file's contents first and then determine whether to continue or not. Not what I would consider elegant, but it would work.
April 14, 2010 at 8:20 pm
Firstly, Thanks a lot everyone.
Really Helped me to solve this issue :))
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply