The Usual Tech Ramblings

Batch Loading Data Into SQL... SSIS Errors

We’ve been having a rough week at work. A product release didn’t go so smoothly, and resulted in servers crashing quite consistently throughout the day, and caused us to call a “rollback”. As we had to do this, and had no real signs of what was causing the crashes, we started looking to the logs for guidance…

The first step was handling our log files. Our log files aren’t exactly small, but not too huge either. The server that handled the least load on Monday generated a 1.2GB log file. Sufficiently sized that doing any kind of work on it would take some time, so we decided to try importing it into a database for processing. This is generally an easy task from enterprise manager. SQL 2000 had it easy, SQL 2005 not so much.

When attempting to import the text file, it passes the work off to the SSIS engine, which then uses various packages to actually do the work. It’s wizard driven, so just click a few buttons, select the delimitation, the usual kind of stuff (like opening a flat file in Microsoft Excel).

The problem I had was when it came to the last step, the actual execution, that I had an issue. When launching, it created the table it was going to put the data into, validates the table to make sure all is well, then attempts to validate the text file. This is where things went down hill.

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (43). (SQL Server Import and Export Wizard)

This was the lovely error I was created with. Not too helpful, after all, without any details, what does “product level is insufficient” mean? Did I miss a service update that crippled my version of the SQL server that I was installing to?

Trusty Google guided my way over the Microsoft forum posting that detailed the same error message, along with a comment by Michael Entin, who had been helping the original poster, and made the comment…

Thanks for reporting this problem, it seems like we have a bug here.

Some components of SSIS (e.g. designer) are installed and work with ‘Workstation Components’ install. Other components of SSIS (e.g. service, dtexec, and dtexecui) require installation of ‘Integration Services’.

Full details are about a third of the way down the article mentioned above. It basically says that you cannot install the server portion of the SSIS system, without running a server class OS. I, am of course, running Windows XP, not considered a server class OS.

The original poster worked around this issue by uninstalling the SQL enterprise tools, and reinstalling the developer versions, which gave him access to installing all the SSIS suite. I unfortunately didn’t have the time to tinker with my machine, so I handed the task off to our DBA who imported it for me, he’s nice like that.

So lesson of the day, SQL keeps track of the products you have installed by using “product levels”. This is likely to be a bitwise type of calculation to figure out what products are installed, and licensed.

As always… disclaimer.