November 10, 2009 at 6:49 am
Hi,
I am trying to config a SSIS package to run using Dynamic flat file and SQL Server connections. I'm failing to do so.
I created an XML config file and checked the properties corresponding to Flat File/SQL Server connections
However when I port this package to a different box and change the values in the config file it still fails to connect.
I get this error message.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 [53]. ". End Error Error: 2009-11-10 08:37:16.84 Code: 0xC020801C Source: Import Work Data SQL Server Destination [9369] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SQLServer" 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: 2009-11-10 08:37:16.84 Code: 0xC0047017 Source: Import Work Data SSIS.Pipeline Description: component "SQL Server Destination" (9369) failed validation and returned error code 0xC020801C. End Error Error: 2009-11-10 08:37:16.84 Code: 0xC004700C Source: Import Work Data SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2009-11-10 08:37:16.84 Code: 0xC0024107 Source: Import Work Data Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:36:51 AM Finished: 8:37:16 AM Elapsed: 25.6 seconds. The package execution failed. The step failed.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
I have set the Delayed validation as true, still no luck so far.
Any help is greatly appreciated.
Thanks.
November 10, 2009 at 9:58 am
Can't tell a lot from what you've provided here, but one thing that stands out in your error text is that you're using a SQL Server Destination. While this is valid, the SQL Server Destination object is very touchy, and must be configured just so, lest you start seeing unexplained errors. I would recommend replacing the SQL Server Destination with an OleDB Destination. Granted, it's not optimized specifically for SQL Server, but I use this object exclusively in place of the more troublesome SQL Server destination.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply