February 11, 2014 at 6:15 am
Why doesn't SQL Agent see this as an error and fail the job? How can I get SQL Agent to fail on this?
Message
Executed as user: SQL\sqlagent. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:19:46 PM Error: 2014-02-10 13:19:47.09 Code: 0xC020200E Source: Data Flow Task Flat File Source 1 [454] Description: Cannot open the datafile "D:\Jobs\Zip\ziplist5\ziplist5.txt". End Error Error: 2014-02-10 13:19:47.09 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Source 1" (454) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 1:19:46 PM Finished: 1:19:47 PM Elapsed: 0.656 seconds. The package executed successfully. The step succeeded.
February 11, 2014 at 7:29 am
You should look for settings inside the package itself. Technically it didn't fail:
The package execution returned DTSER_SUCCESS
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2014 at 8:30 am
ok, I ended up creating a batch file as step 1 to see if the file is there and set an exit code when it's not there, which causes sql agent to error out.
February 11, 2014 at 12:43 pm
Maybe you want to make a system that fails gracefully when a file is not present.
The easiest method imo is to use a script task, where you can check the existence with some .NET.
Put the result in a variable. Use an expression on the precedence constraint to decide if the rest of the package should be executed.
Either way, the package succeeds and nothing crashes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 13, 2014 at 12:27 am
Koen Verbeeck (2/11/2014)
Maybe you want to make a system that fails gracefully when a file is not present.The easiest method imo is to use a script task, where you can check the existence with some .NET.
Put the result in a variable. Use an expression on the precedence constraint to decide if the rest of the package should be executed.
Either way, the package succeeds and nothing crashes.
Another option, which does not require any coding, is to put everything inside a FOREACH loop container with an appropriate file spec.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 16, 2014 at 11:56 pm
Phil Parkin (2/13/2014)
Koen Verbeeck (2/11/2014)
Maybe you want to make a system that fails gracefully when a file is not present.The easiest method imo is to use a script task, where you can check the existence with some .NET.
Put the result in a variable. Use an expression on the precedence constraint to decide if the rest of the package should be executed.
Either way, the package succeeds and nothing crashes.
Another option, which does not require any coding, is to put everything inside a FOREACH loop container with an appropriate file spec.
That is some good thinking sir 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply