Symptoms
When you schedule a Microsoft SQL Server 2005 Integration Services (SSIS) package to run as a SQL Server Agent job, you receive error messages that resemble the following when the job runs:
OnError,SQL1V5,MyDomainMyAccount,Execute Source To Common Package,{BD5416B7-B16A-4788-99C9-5A1F674E326B},{E4B9AD56-627E-4801-A262-C9EFA9F163AC },11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: «Microsoft JET Database Engine» Hresult: 0x80004005 Description: «Unspecified error».
OnError,SQL1V5,MyDomainMyAccount,DataSourceImport,{91A4C5D3-AC64-4DF8-B49E-7 3CFC9DAF979},{E4B9AD56-627E-4801-A262-C9EFA9F163AC},11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: «Microsoft JET Database Engine» Hresult: 0x80004005 Description: «Unspecified error».
OnError,SQL1V5,MyDomainMyAccount,Execute Source To Common Package,{BD5416B7-B16A-4788-99C9-5A1F674E326B},{E4B9AD56-627E-4801-A262-C9EFA9F163AC },11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071611876,0x,The AcquireConnection method call to the connection manager «MyConnectionManager» failed with error code 0xC0202009.
OnError,SQL1V5,MyDomainMyAccount,DataSourceImport,{91A4C5D3-AC64-4DF8-B49E-7 3CFC9DAF979},{E4B9AD56-627E-4801-A262-C9EFA9F163AC},11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1071611876,0x,The AcquireConnection method call to the connection manager «MyConnectionManager» failed with error code 0xC0202009.
OnError,SQL1V5,MyDomainMyAccount,Execute Source To Common Package,{BD5416B7-B16A-4788-99C9-5A1F674E326B},{E4B9AD56-627E-4801-A262-C9EFA9F163AC },11/21/2006 4:11:31 PM,11/21/2006 4:11:31 PM,-1073450985,0x,component «MyConnectionManager» (72) failed validation and returned error code 0xC020801C.
Cause
This issue occurs because the SQL Server 2005 Agent proxy account does not have permission for the Temp directory of the SQL Server Agent Service startup account.
This issue occurs because the SQL Server 2005 Agent proxy account does not have permission for the Temp directory of the SQL Server Agent Service Startup account.
Resolution
To resolve this issue, you must change the permissions for the Temp directory of the SQL Server Agent Service startup account. Grant the Read permission and the Write permission to the SQL Server 2005 Agent proxy account for this directory.
Status
This behavior is by design.
More Information
The owner of the job determines the security context in which the job is run. If the job is owned by a SQL Server login account that is not a member of the Sysadmin fixed server role, the SSIS package runs under the context of the SQL Server Agent proxy account.
If you want the SQL Server Agent proxy to run jobs that connect to an instance of SQL Server, the SQL Server Agent proxy account must have correct permissions to the instance of SQL Server.
If you want the SQL Server Agent proxy account to run a job that runs as an SSIS package, the SQL Server Agent proxy account must have the Read permission and the Write permission to the Temp directory of the SQL Server Agent Service startup account.
Need more help?
- Remove From My Forums
-
Question
-
Hi,
I have received the following error with when executing an SSIS package with a SQL Server database as a data source where I have read only access; —
Error: 0xC0202009 at my_SSIS_component, my_database: 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: «Protocol error in TDS stream».
An OLE DB record is available. Source: «Microsoft SQL Server Native Client 10.0»
Hresult: 0x80004005 Description: «Communication link failure».
An OLE DB record is available. Source: «Microsoft SQL Server Native Client 10.0»Hresult: 0x80004005 Description: «TCP Provider: An existing connection was forcibly closed by the remote host.
«.
An OLE DB record is available. Source: «Microsoft SQL Server Native Client 10.0» Hresult: 0x80004005 Description: «Communication link failure».
An OLE DB record is available. Source: «Microsoft SQL Server Native Client 10.0» Hresult: 0x80004005 Description: «TCP Provider: The semaphore timeout period has expired.
«.
Error: 0xC0047038 at my_SSIS_component, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component «my_database» (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine
called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.Service operating system; —
Microsoft SQL Server 2008 (SP2) — 10.0.4064.0 (X64) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Where I understand Windows NT 6.0 SP 2 is also known as Windows Server 2008 Service Pack 2.The error appears to occur intermittantly. Any guidance on how to arrive at a resolution would be greatly appreciated.
Kind Regards,
Kieran.
If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood
-
Edited by
Wednesday, October 5, 2011 2:04 PM
-
Edited by
Answers
-
Hi,
The solution was that the VPN between a Microsoft TMG firewall and a Cisco ASA (mixed vendor) under load kept collapsing the VPN tunnel.
The fix was to replace Microsoft TMG with a cisco Firewall so the VPN is now cisco-to-cisco, resulting in a much more stable connection. Also a router was purchased with a higher capacity to cope with the additional traffic.I then performed an initial test where I sucessfully copied a 1/2 tb file using Windows file manager from the source server to the destination server over the new Virtual Private network connection. Where performing a file copy when the problem initially
arose is where I convinced my collegues that the core issue was network related and not related to the configuration of my SSIS package.After receiving the results of the successful 1/2 tb file copy. I then executed my SSIS package over night over this new VPN connection, and the package reported successful execution within SQL Server Job Agent.
Hopefully people out there will use this solution and hopefully have less pain than me resolving it.
If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood
-
Edited by
Kieran Patrick Wood
Monday, March 12, 2012 8:36 PM -
Marked as answer by
Kieran Patrick Wood
Monday, March 12, 2012 8:36 PM
-
Edited by
I’m posting this across AX, CRM and GP forums since, if you are doing BI/Data Warehouse ETL work against any of these products, you may run into this problem.
I recently restructured a SSIS package to allow several Data Flow tasks to run simultaneously. To support this, I contained discrete operations involving independent table groups into Sequence Containers. The first time I ran the package, I received a OLD DB Error 0x80004005 on the Data Write component in the Control Flow. Interestingly, this only seemed to happen if I truncated the table before I did the write operation.
A little research later (http://connect.microsoft.com/SQLServer/feedback/details/573638/ssis-package-failes-with-ole-db-error-when-using-transaction-support-on-sequence-container), and I discovered that I could eliminate this problem by changing the table load from “Table or View – Fast” to just “Table or View”.
Everything now works fine. Hope this save you some time next time you see this error.
Good loading!
** Update 4/28/2013 **
The only problem with the above solution is that by using the “Table or View” setting in the table load component of the data flow task, I ended up getting cursor inserts into the table. REALLY SLOW! When I started to drill down on the error even farther, I also found I was getting the error on reads with a complete error “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 Native Client” HResult: 0x80004005 Description: “Connection is busy with results for another command”. ”
So, I instead changed the properties of the Connection Manager, setting RetainSameConnection connection from True To False. According to this article (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8f1fd538-a5c8-4ae7-96fd-3a4e51100e32/), this message happens when you have multiple parallel running SSIS tasks sharing the same connection. This was exactly the case with my package.
Now, by doing this, you lose some functionality. Specifically, you can no longer run a series of packages inside a single transactions (ref this article http://consultingblogs.emc.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx). Also, in theory, you are opening a connection per task and that consumes additional resources on the server – you may hit a limit if you use this indiscriminately.
