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.

 

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.




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

• Expend the Computers->Expend My Computer->  to find Local DTC
• Right Click on the Local DTC to open the property window.

• Go to Security Tab and enable the Network DTC Access. For more information on other options, See Enable Network DTC Access.

 

Reference

Enable Network DTC Access