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 FilesMicrosoft SQL Server80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn…
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 FilesMicrosoft SQL Server100DTSBinndtexec.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.
