Search code examples
ssisssmsbids

SSIS Error Code DTS_E_OLEDBERROR: 0xC0202009

I created a new package today, it gets info passed down from a Master package that runs other packages as well. My master package pulls the connection info from a config file at runtime. Protection level = DontSaveSensitive. Using Windows Authentication. It works fine locally but when I run it from a job it gives me the error below.

Any ideas or constructive comments are welcome!

Code: 0xC0202009 Source: DFT - Insert Into StageSites Source DB TEICommon [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Invalid column name 'SuspendedDate'.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Invalid column name 'FirstShipmentDate'.". End Error Error: 2016-10-28 10:38:02.50 Code: 0xC004706B Source: DFT - Insert Into StageSites SSIS.Pipeline Description: "component "Source DB TEICommon" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2016-10-28 10:38:02.50 Code: 0xC004700C Source: DFT - Insert Into StageSites SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-10-28 10:38:02.50 Code: 0xC0024107 Source: DFT - Insert Into StageSites Description: There were errors during task validation. End Error

Update: [Fixed] I solved the issue by updating my schema in the database my ETL query was pulling from.

Solution

  • Here are your actual errors:

    "Invalid column name 'SuspendedDate'.".

    "Invalid column name 'FirstShipmentDate'.".

    You are querying a table (in the component Insert Into StageSites) , expecting those columns to exist when they do not. Since you say it works locally but not from a job, the most likely explanation is that your DataSource is connecting to a different server when the job runs it than the one it connects to locally. Check the connection string in the .config file on the server that the job points to.