Tuesday, May 29, 2012

SSIS Errors


Recently I encountered some ETL errors after deploying the SSIS packages from a single SQL server environment to a two SQL servers environment.

Error One:

Error 0x8000FFFF while preparing to load the package. Catastrophic failure.
 

Problem and Resolution:

This error message is very vague, it dese not provide enough info to figure out what's going on.  When I google it, the only clue is invalid disk address.  Since I just pushed some new packages to a new server, I suspect the client tool calling the ETL packages may not be able to locate it for some reason.
After closer examining the Parent package that hosts this package, I find this package is outside the sequence container even thought it looks like in it.  The step to replace the physical path for the package before this sequence container has been skipped and causes this error.


imageimage

 

Error Two:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025. "The partner transaction manager has disabled its support for remote/network transactions.". Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D025.
A similar error could be:
System.Transactions.TransactionException: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025) ---> System.Runtime.InteropServices.COMException (0x8004D025)
 

Problem and Resolution:

If there is a SSIS package that has the TransactionOption=”Required”, you will need to have DTC turns on.

image
image

When the package is going to be executed on a multi SQL servers environment, you will need to enable Network DTC Access on both Servers or change the SSIS TransitionOption to not be “Required” to fix the error.
Here is the step to enable the Network DTC Access:
  • Open Component Services in Administrative Tools
image
  • Expend the Computers->Expend My Computer->  to find Local DTC
  • Right Click on the Local DTC to open the property window.
image
image
 

Reference

Enable Network DTC Access

1 comment:

  1. Amazing blog! Do you have any hints for aspiring writers? I’m planning to start my own website soon but I’m a little lost on everything. Would you suggest starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely overwhelmed .. Any suggestions? Thanks!
    Wifi Booster

    ReplyDelete