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

Deployment Options for BISM Project

 

There are two Query Mode options you could use when you deploy the BISM project to the AS Tabular Model server.

• In-Memory
• DirectQuery

By default, data in tabular model is processed, compressed using the xVelocity in-memory analytics engine (VertiPaq).  This in-memory columnar storage engine has been optimized for high performance analysis and exploration of data.  It provides fast query times for aggregation queries.

However, there are some drawbacks:

• Data is not updated when the source data changes. Model needs to be processed to refresh the data.
• When you turn off the computer hosting the model, the cache is saved to disk and must be reopened when you load the model.
• The save and load operations can be time-consuming.
• Need lots memory to hold the large fact data

On the other hand, DirectQuery mode uses data that is stored in a SQL Server database. It lets users retrieve data directly from a SQL Server data source in  real time.  This mode also lets you create models and build reports for large data sets that cannot reside in memory.

Here are additional benefits:

• The data is guaranteed to be up-to-date.
• Use the advantage of provider-side query acceleration, i.e. SQL 2012 column indexes.
• Could use row-level security provided by the backend  database.
• Analysis Services can perform optimization to ensure the query plan against the backend database will be as efficient as possible.

There are some design considerations if you are planning to use DirectQuery mode:

• During design phase, you may need to use Preview or Filter function to load subset data into your project.
• MDX queries are not supported for a model in DirectQuery mode. You cannot use PPS or other clients that only issue MDX queries to consume it.
• Currently this model only supports one data connection. You cannot query two or more SQL Servers from a DirectQuery enabled model.
• Calculated columns and some DAX functions are not supported. You may need to use SQL View or other technique for that purpose.

DirectQuery supports a hybrid deployment mode that can use either the cache or the relational source. For more information, see DirectQuery Mode (SSAS Tabular).

 

Reference

Formula Compatibility in DirectQuery Mode

DirectQuery Mode (SSAS Tabular)

White Paper: Using DirectQuery in the Tabular BI Semantic Model