Focusing on setup and development using SQL Server products from 2000 to 2008 and beyond. Also about anything geeky that compels me to write a blog entry.

Thursday, September 3, 2009

SSIS Packages fail on ODBC connections after .NET Framework 3.5 SP1 or 2.0 SP2

If you work with ODBC connections through third-party ODBC drivers such as MySQL, you may have already experienced the pain of this Windows Critical Update. This is my tale of woe.

I was innocently applying critical Windows Updates to one of my SQL Servers, one is the .NET Framework 3.5 SP1. I read the details and saw that it also includes SP2 for both .NET 2.0 and 3.0, but still it is a critical update. As always, I there may be some bumps in the road but I figure I will just work through them. Updates applied. Basic database and reporting all test fine. So far so good.

The next morning, all of my SSIS packages that pull data through ODBC connections have failed, and as a result all of my subsequent scheduled reporting has also failed. That is what I call having Monday on Wednesday.

I know what happened due to my daily email report of all jobs from the last 24 hours, sorted by the errors first. In checking my SSIS log files, I find this error:

OnError,[Server],[UserAccount],[TableName],{GUID1},{GUID2},9/1/2009 11:15:48
PM,9/1/2009 11:15:48 PM,-1071607767,0x,SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source"
(31)" failed because error code 0x80131937 occurred, and the error row
disposition on "output column "[ColumnName]" (1848)" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the
failure.


After some research, I find that this is a known issue on an msdn blog. It seems that .NET Framework 2.0 SP2 changes the way that the OdbcDataReader checks for Nulls, resulting in compatibility issues with some ODBC drivers. It impacted both the MySQL and CVIEW (for ISAM connections) ODBC drivers that I was using to pull in all my data.

As explained in the excellent blog post, the preferred option is to add error output to your SSIS packages, thereby handling the bogus errors. Unfortunately I have six or seven packages with twenty or more DataReaders in each package, so it would not be a quick fix. The blog mentions option #2 is to roll back your updates, which for me was the only option. I had production people waiting.

Due to the 3.5 constraints, I had to remove .Net 3.5 SP1, then .Net 3.0 SP2, and finally .Net 2.0 SP2. Then, because 3.0 and 2.0 updates are slipstreamed into the 3.5 SP1 update, at that point I had zero .Net frameworks installed. I had to reinstall .Net 2.0 and SP1, which is all I need for my SQL 2005 and related functionality. It took roughly 20-30 minutes to uninstall and reinstall along with reboots, not including any download requirements.

Afterward, I started playing with the website and realized the IIS default page was not even available. I looked at properties in the default website, looked at the ASP.NET tab... bingo, the ASP.NET version is set to... blank. I click the drop-down and see .Net 2.0 is available, but knowing that the scope of the settings goes far beyond this setting, I went back to the command line.

I knew I needed to run regiis, but I don't do this frequently so I searched, found a refresher, and ran the following command:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Aspnet_Regiis -i

Because I am on a 64-bit server, I was wroking in the Frameword64 subdirectory, but if you are on 32-bit, it is the same but you browse to the Framework subdirectory.

Now my baseline ASP.NET is configured in IIS. I browse to the "/reportserver" directory in IE8 and get a (403) Forbidden error. That's an improvement.

I looked around in various places at various settings for a bit, just trying to help me think. In the Reporting Services Configuration Manager, it hit me that I had just made a mess of IIS and in the process the virtual directories had been through the ringer. So under Report Server Virtual Directory and Report Manager Virtual Directory, I checked the "Apply default settings" checkbox and applied the changes.

Boom, back in business. Reports were up and running and I was able to run my SSIS package to generate all my scheduled reports. Then I remembered one other little ASP.NET application that I wrote for managing the report schedules. Tried to pull it up in the browser and it didn't open.

After looking at IIS settings for a while, I finally found the problem. So I right-click on the Virtual Directory, go to Properties-->Documents tab and default.aspx is not in the list. In the process of resets, apparently the default content page list had been reset on my virtual directory. I added default.aspx to the list and boom, everything is back up and running.

I hope that at some point Microsoft makes allowances for this issue in their .Net updates but in the meantime, doing without .Net 2.0 SP2 is fine by me.

No comments:

Post a Comment