Thursday, March 27, 2008

SSIS Error executing a package in a 64 bit server

Last week I get into trouble when I was developing a SQL Server Integration Services package to consolidate some information into a Data Warehouse. The package consists of taking some information from text files, performs some transformation with the data via a script task, and updates a SQL table. When I execute the package in the development environment, it does with no problems.
When I deploy the package into the production server, a four 64bit-processors machine, and executes it from a SQL Server Agent job, I got the following error:


- DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component ...



Digging and "googling", I discover, despite the confusing (or not?) message from SSIS, that the problem was that the script component of the package was pre-compiled for 32-bits and the server where I was trying to execute the package was a 64-bits one.

There is an option of the "Script Component" that allows to compile in execution time the script, but it's a requirement to pre-compile the script if you want to execute them in a 64-bits server. Amazing!

The workaround consists of executing the package with the dtexec utility instead of as a SSIS ordinary package. In all 64-bits SQL Integration Services servers, the SQL installer installs two versions of the dtexec utility, one for the 64-bits package and another one for 32-bits compatibility executions.

When defining the SQL Server Agent job that executes the process, we should change the job type to "Operating System" and type the command line arguments for the dtexec utility properly to execute the package.

The final command for the job was:


"D:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\TestSSIS32bits" /SERVER "." /CONFIGFILE "C:\Projects\TestSSIS32bits.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V


and when I press the "Execute" button the result was SUCCESSFUL: