Monday, September 2, 2013

DTExec issues in multi instance SQL Server box

The purpose of this blog is to highlight a problem which I faced while automating an ETL process recently and to suggest a solution for it.
The scenario was as follows.
There were a set of packages which were developed in SSIS 2008 R2 and which were deployed to a network share. The packages need to be executed as per a convenient schedule. The packages were completed and were copied onto a network share path. The sql agent was using a dtexec call to execute the package as below

dtexec /F "full package path here" /SET "/Package.Variables[User::VariableName].Properties[Value]";"variable value here"

On execution the package was throwing an error like

The product level is insufficient for component "Fuzzy lookup" (109)

The error message clearly suggested the issue was with the product version used. I checked the SSIS version installed on the server and verified that the version was same as that of development environment. This really confused me as I couldn't understand why it should throw an error as above when things were working fine in dev machine having the same version.
To investigate further, I requested for admin access on the server machine and just checked through installed programs and noticed that it had another instance of sql server installed parallely whose version was 2005. This really stroke a chord with me.
I went into the Environment variables section of the machine and checked for the value for PATH variable which was as below

...C:\Program Files\Microsoft SQL Server\80\Tools\Binn;C:\Program Files\Microsoft SQL Server\90\DTS\Binn...

As I expected it had the path value of SQL Server 2005 binn folder coming before SQL 2008 R2's entry as it was the default instance.
Due to this, the dtexec for sql 2005 version will get called by default which was the reason for the error as packages were developed on 2008 R2.
The solution was to explicitly call the sql 2008 R2 dtexec instance using the correct full path as below

"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /F "full package path here" /SET "/Package.Variables[User::VariableName].Properties[Value]";"variable value here"

This time the correct instance of dtexec got invoked and hence package ran successfully giving us the desired output.
This clearly suggests us to call dtexec explicitly using the full path especially when multiple instances are present in the same box to ensure correct instance version was invoked.
Hope this blog will help out any people who face similar difficulties due to multiple instances with varied versions existing on the same server.