Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SSIS package - graceful exit when nothing to do

Status
Not open for further replies.

jjefferson

Technical User
Oct 8, 2001
98
US
I have an SSIS 2008 package that is working fine. There is a script task at the very beginning that looks for the file it wants to import. I have a precedence constraint between that and the next step to test the success of the script task.

When I run this manually from VS, it works fine and stops execution if the file does not exist.

I've moved this onto the server now, and set up a SQL Agent job to execute the package on an hourly basis. It is working fine here, too, with one small annoyance.

When there is no file to import, the job history records a failure. Generally this is okay, except that there is no distinction between this (an expected condition) and a "true" failure.

Any recommendations on what I can either add to the package to exit as if everything succeeded from just this one task in the flow (don't want to exit gracefully elsewhere as that would be a true failure I would want to see), or add something to the Agent job steps perhaps?

Thanks!
 
Instead of testing for the success of the of the script, create a variable. Set the script to store true or false depending if the file exists.

Then change the constraint to use this parameter.

That way the script task will succeed and you wont get a failure.

I know this becuase i have just done it! :)

Dan

----------------------------------------

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
Thanks, SQLScholar. That is what I am doing already. I see that my original post does not clearly specify this, but the script task checks for the existence of the file, and sets a variable. The precendence constraint then checks that variable in an expression.

If I run this package from Visual Studio, it works as expected; if the file does not exist the package stops execution.

When I put this package into a SQL Agent job, it also stops execution properly. But the job history shows a failure when this is really a "soft" fail. No file, sleep the job and look again in an hour.

I'm trying to get this particular stop of execution to not show up as a failure in the job history, so I can see any "real" failures.

Perhaps this is better a SQL Agent Job question instead of SSIS, but I thought I'd try here first if there were some technique to gracefully exit a package so it doesn't do any of the other steps, but not appear as a "failure".

Thanks for any other ideas!
 
OK. I am getting a little confused here.

So if you run it in BIDS with a file does it have ANY red elements?

----------------------------------------

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
No, no red elements in BIDS either way. If the file is there, the data flow elements execute. If no file is there, the package ends prior to the data flow (as expected) with no red elements.

The issue appears to be the status raised by the package when it terminates at the script task. The SQL Agent job running it is getting some status that there was some error and that the package did not complete successfully, same as any other error (like a connection string problem, or corruption in the file to be imported, a change in the target SQL Server table, etc.).

It is really just a cosmetic/annoyance issue. The job history is just filled with these once-per-hour "failures" which I don't want to see. If there is a real error, I would have to look through dozens of these messages in the history to find the one that I care about.

So, I was thinking there was either some task I could branch to from the script task to just end the package with a "success" indication when the source file isn't there, or some enhancement I could put on the precendence constraint to terminate the package in such a way that the Agent job didn't detect it as a failure.

Hope I haven't confused more. Thanks for your help.
 
Nope - although i cant directly ask why - it makes more sense.

Things to look at:

Make sure the script task sets the
Dts.TaskResult = Dts.Results.Success

The only other option i can think about is on the script task there is property called Failpackageonfailure. If you set this to false then ANY errors on this script will not fail the package.

Cant think of anything else without it being in front of me.

Dan

----------------------------------------

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
It turns out that I just don't know how to read the stupid messages in the job history :(

I found that by setting Delay Validation to True on the data flow task (which expects the file to be there), the Agent job history now shows a success when the earlier script task bails on a missing file. It was the validation step of the data flow task that was throwing the failure that I was trying to eat.

I am properly red-faced and old enough to know to make sure I understand what the process is complaining about; I just failed to do so this time.

Thanks for your help; your suggestions forced me to look in areas I was ignoring.
 
Dont be so hard on yourself.

Sometimes i have a question - post it, then answer it myself. Its sometimes just as easy as having to write it down. Other times its just a second pair of eyes.

My old boss used to say you need a cardboard coder. Its just a cardboard cutout of a coder, who if you have a problem you can then show what the problem is.

Take care

Dan


----------------------------------------

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top