Tuesday, October 16, 2007

SSIS, 64-bit and a breakpoint

Despite of 2 service packs and the numerous hotfixes we installed since the release of Sql Server 2005, we still discover problems with SSIS much too often. This time, it was some weird message in our acceptance environment telling me the "Script files failed to load". I had thoroughly tested the package on our development environment, so I was amazed to hear it failed on another environment.

A quick search on the specialized forums returned some hits - mostly related to the Script task the package contained - but nothing really described the conditions I faced. The only difference I could think of was the fact that the acceptance environment was a 64-bit edition, unlike development. In such a case, the settings of the Script task are quite simple: the PrecompileScriptIntoBinaryCode flag should be on.

There was a non-active breakpoint however that caught my attention which I hadn't noticed before because the Script task was not part of my changes to the package and the tests had never halted at that break. After removing the breakpoint, the package effectively ran without a problem.

It's a shame we had to discover this bug the hard way, but it proves that an acceptance environment should stick as close to production as possible. In this case, it payed off.

Monday, October 15, 2007

SSIS's interaction with DTC

I encountered some locking problems while developing an SSIS package today. The purpose of the package is to upload new content from flat file to a table. It contains basically two control flow tasks - one clearing the table (TRUNCATE TABLE) and another one filling it with a data flow task - that require a transaction. But, while executing, the package blocked at the start of the data flow task.

Investigating the blocking, I encountered some SPID -2 owned locks in sys.dm_tran_locks, thus blocking the data flow task from retrieving the necessary metadata from the database at the PreExecute event. I fiddled around with the setting of the TransactionOption and IsolationLevel of the different components, but nothing resolved my problem adequately. With the help of profiler however, I could determine that SSIS did not propagate the DTC transaction - started for the SQL task - to the Data Flow task.

The blocking can be resolved by either changing the ValidateExternalMetadata attribute of the OLE DB Destination or changing the TRUNCATE TABLE of the SQL task into a DELETE statement.

The reason why both of these solutions have such an impact remains unclear to me. It's true that each of these modifications affect the locks that are required by the package, but that doesn't explain the fact that the Data Flow task was not enlisted appropriately into MSDTC.