SSIS package fails on error 0xC0202009

• chris.stuart

  SSCarpal Tunnel

  Points: 4965

  More actions

  June 13, 2012 at 12:52 am

  #392505

  Hi all

  I've got a SSIS job which imports data to a linked server. The source is a flat file and the destination the linked server. (I'm doing it from my main server as I don’t have full access on the linked server and for other reasons as well)

  The linked server is taken down during the evening for backups, but my first step in the SSIS package is to test if the linked server is up via a Stored procedure

  The SP check first with a ODBC_ping and then sees if the result contains Connected to SQL Server"

  Now this all seems to work fine, but when the server is down, it fails on the connection to the server itself, before it even can get to check or call the main server

  i.e.

  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Server.LinkedServer" failed with error code 0xC0202009.

  I believe there’s a delay validation is SSIS, will this option sort out this problem, or should I rather create a SSIS package which first just checks the SP on the main server and then call a second package which will import the data and which will also contain the connection to the linked server.

  Just to add, once the linked server is back up and running, my package runs and imports the data, but its just the error messages which I receive everyday because the package failed during the night, which is bothering me

  The full error is as follows

  Executed as user: XXXXX. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 02:00:47 AM Error: 2012-06-13 02:01:04.70 Code: 0xC0202009 Source: LoadToLinked Connection manager "Server.XXXXX" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ". End Error Error: 2012-06-13 02:01:04.72 Code: 0xC020801C Source: 54 Copy data from flat file to XXXXX DB To XXXXX - Tbl_XXXXX [312] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Server.XXXXX" 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: 2012-06-13 02:01:04.75 Code: 0xC0047017 Source: 54 Copy data from flat file to XXXXX DB SSIS.Pipeline Description: component "To XXXXX - Tbl_XXXXX" (312) failed validation and returned error code 0xC020801C. End Error Error: 2012-06-13 02:01:04.77 Code: 0xC004700C Source: 54 Copy data from flat file to XXXXX DB SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-06-13 02:01:04.78 Code: 0xC0024107 Source: 54 Copy data from flat file to XXXXX DB Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 02:00:47 AM Finished: 02:01:04 AM Elapsed: 17.141 seconds. The package execution failed. The step failed.