January 30, 2017 at 7:16 pm
I do a negative testing. I renamed dstConfig on purpose. I want SQL Agent job to fail.
It runs for 5 minutes and then fails with long error that has nothing to do with dtsConfig missing file
Why 5 min?
Is there any way to force SSIS to fail right away with the correct error?
Error
-----------------------
Message
Executed as user: BBL\s_scene_proxy_u. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:09:34 PM Error: 2017-01-30 21:09:35.06 Code: 0xC0202009 Source: BNS_TXN Connection manager "Db Conn" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'BBL\s_scene_proxy_u'.". End Error Error: 2017-01-30 21:09:35.06 Code: 0xC020801C Source: Data Flow Task OLE DB Destination [14] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Db Conn" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2017-01-30 21:09:35.06 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Destination failed validation and returned error code 0xC020801C. End Error Error: 2017-01-30 21:09:35.06 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-01-30 21:09:35.06 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:09:34 PM Finished: 9:09:35 PM Elapsed: 0.406 seconds. The package execution failed.
January 30, 2017 at 7:52 pm
RVO - Monday, January 30, 2017 7:16 PMI do a negative testing. I renamed dstConfig on purpose. I want SQL Agent job to fail.
It runs for 5 minutes and then fails with long error that has nothing to do with dtsConfig missing file
Why 5 min?
Is there any way to force SSIS to fail right away with the correct error?Error
-----------------------
Message
Executed as user: BBL\s_scene_proxy_u. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:09:34 PM Error: 2017-01-30 21:09:35.06 Code: 0xC0202009 Source: BNS_TXN Connection manager "Db Conn" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'BBL\s_scene_proxy_u'.". End Error Error: 2017-01-30 21:09:35.06 Code: 0xC020801C Source: Data Flow Task OLE DB Destination [14] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Db Conn" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2017-01-30 21:09:35.06 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Destination failed validation and returned error code 0xC020801C. End Error Error: 2017-01-30 21:09:35.06 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-01-30 21:09:35.06 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:09:34 PM Finished: 9:09:35 PM Elapsed: 0.406 seconds. The package execution failed.
SSIS does not treat a missing config file (or, for instance, missing records in a config table) as an error. It will show a warning message if warnings are logged.
I suspect that the time taken to fail is probably the time taken for several connections to timeout.
January 31, 2017 at 2:29 am
You could make the first step of your SQL Agent job a call to xp_fileexist, but it's an undocumented SP, so might not consistently behave the way you expect across versions of SQL Server.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply