December 16, 2009 at 4:53 am
In my SSIS package i have a task that uses OLE DB to connect to an access datasource and copy data to a SQL Server database. Ive tested each individual connection and they check out ok. Because I want to be able to rollback the data changes if the task fails, Ive set The isolationLevel to 'ReadCommitted' and the TransactionOption to 'Required'
When I run the task, it fails with these errors
OnTaskFailed,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{17364205-3163-47E5-A5E9-1A4125FF1CC4},16/12/2009 11:27:06,16/12/2009 11:27:06,0,0x,(null)
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{29E9936F-26B4-4640-8ECC-56A3BD963E0C},16/12/2009 11:35:34,16/12/2009 11:35:34,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "NBD Co" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{29E9936F-26B4-4640-8ECC-56A3BD963E0C},16/12/2009 11:35:34,16/12/2009 11:35:34,-1073450982,0x,component "OLE DB Source" (54) failed the pre-execute phase and returned error code 0xC020801C.
OnTaskFailed,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{29E9936F-26B4-4640-8ECC-56A3BD963E0C},16/12/2009 11:35:34,16/12/2009 11:35:34,0,0x,(null)
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,-1073450780,0x,The pipeline received a request to cancel and is shutting down.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (54) returned error code 0xC02020C4. 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.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnTaskFailed,PSLW16792,B2B\wheelerm,Step02a Transfer Company into EGiFeed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{5D7C617A-B6B2-4959-A036-499B80D5A660},16/12/2009 11:41:30,16/12/2009 11:41:30,0,0x,(null)
If I set the Isolation back to the defaults of 'Serializable' and the TransactionOption to 'Supported' the process works.
Can anyone tell me what the problem is ? I need the task to run in a transaction so I can rollback and start from the point of failure. (Im using checkpoints)
December 16, 2009 at 11:03 am
[font="Comic Sans MS"]
Here is a step by step guide on how to use transaction in SSIS - you may have a look and see if you are doing anything wrong/differently :
http://www.mssqltips.com/tip.asp?tip=1585
Also - an extract from above:
The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction. If you execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, you will get an error message like the following ...
Also you may like to check this out:
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply