December 20, 2011 at 1:59 am
Hello all,
I have a question, which hopefully won't require too much time from the person that knows the answer!
I have a very simple SSIS package, which performs a few simple File System Tasks, then exports a database query to a series of flat files.
The initial file system tasks simply move the previous flat files into an Archive folder. I only want to keep the previous set of file - not an entire history, so its all very simple.
If the data export ever fails before the files are created and I need to run the package again, it fails due to the File System tasks bveing unable to find the files to archive. Fine - I changed the Precedence Contstraints to the Completion value, and so this no longer happens. The package continues if the files are not present.
My question is regarding the way SQL Server Agent sees this package as having failed. I can ignore the reported failure, but it plays havoc with my OCD! Is there any way to make SQL Server Agent report this as a successful execution of a package?
Advice appreciated,
Paul
December 20, 2011 at 2:02 am
on the package property's, set ON FAILURE report SUCCESS..
see attachment.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 2:07 am
Thanks Henrico.
However I think that option is a little more drastic than I had in mind. I was hoping that I would still be able to report failure if the dataflow tasks failed - it is only the file System Tasks that I want to "ignore" the status of.
Perhaps this just isn't possible.
December 20, 2011 at 2:09 am
did you build failure action as well into your dtsx package?
for every success you need to anticipate a failure action as well, maybe use send mail or log a message task?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 2:12 am
Paul_Harvey (12/20/2011)
Hello all,I have a question, which hopefully won't require too much time from the person that knows the answer!
I have a very simple SSIS package, which performs a few simple File System Tasks, then exports a database query to a series of flat files.
The initial file system tasks simply move the previous flat files into an Archive folder. I only want to keep the previous set of file - not an entire history, so its all very simple.
If the data export ever fails before the files are created and I need to run the package again, it fails due to the File System tasks bveing unable to find the files to archive. Fine - I changed the Precedence Contstraints to the Completion value, and so this no longer happens. The package continues if the files are not present.
My question is regarding the way SQL Server Agent sees this package as having failed. I can ignore the reported failure, but it plays havoc with my OCD! Is there any way to make SQL Server Agent report this as a successful execution of a package?
Advice appreciated,
Paul
Depending on how many files and folders, one possible way of doing this is to put the FSTs inside a Foreach container (with appropriate file spec) - then, within the container, archive the file. The package will continue happily when all files have been processed. If there are no matching files, control passes to the next task in the package - without any errors.
Another way is to use a script task and code around the possibility of the file(s) not existing.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 20, 2011 at 2:41 am
Brilliant! This works perfectly and is exactly the effect I was looking for.
Thanks very much for your help. That's very much thinking outside the box!
December 20, 2011 at 2:58 am
Thanks for the feedback and no problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply