Friday 20 November 2009

Existing SSIS Packages Generating New Errors (.NET Framework)

I had been using an SSIS package to load in data from a remote database using the Sybase Adaptive Server Enterprise driver on my own machine and on two 64 bit servers.

The load was working fine, but then it stopped working suddenly complaining of error code 0x80131937:

* Error 0xc0209029: Data Flow Task: The "component "Source - Query" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "WhseCode" (12)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)


But the weird thing was that the exact same DTSX package stopped working on my PC and on only one of the two servers - it kept working on one of them.

So I tried everything:

- Stopping the package from failing on error to see what rows would be loaded into the database - every row loaded but every column was set to NULL.
- Rolled Sybase driver back to ASE ODBC 12.5 - this actually worked, but I could not roll the drivers back on the server machines so I had to find another answer.
- I tried several different query formats, but it failed every time.

Then, I stumbled across this thread and the good advice on the .NET framework versions proved to be the answer.

On my local workstation machine and the server which stopped working, I had .NET 3.5 SP1 installed. But on the server which was still working, the .NET framework was 3.5 without SP1.

Basically, I removed all versions of the .NET Framework, reapplied .NET 2.0, 2.0 SP1, 3.0 and 3.5 in order and tested the package between each installation.

Now it's working and the data is loading in just as it should.

I can't believe that .NET 3.5 SP1 would just break ADO like this. I don't have a problem with compatibility issues between versions of .NET, but some explanation or notification would be useful.

Oh well, the things you learn.

1 comments:

Phil Reid said...

A few months later and I came across this issue again. But the difference was that this time we needed to keep .NET 3.5 SP1 installed so I had to find another way round this.

There is a workaround on the SQL Server Support Blog which worked for me:

http://blogs.msdn.com/b/sqlblog/archive/2009/04/09/after-installation-of-net-framework-3-5-sp1-or-net-framework-2-0-sp2-ssis-packages-using-odbc-3rd-party-drivers-may-fail.aspx

Not completely sure why it worked, but my SSIS package now completes and I didn't have to remove .NET 3.5 SP1 so I'm happy with that.

Would prefer if Microsoft hadn't caused this problem in the first place by not making the two versions compatible though...