I had to open up a dts package from SQL 2008 Management Studio and some additional installs are required to open it up. Microsoft documentation mentioned that ‘SQL Server 2000 dts designer components’ and ‘2005 backward compatibility tools’. I installed both of them today morning, but still was unable to open the package.
That’s when one of the DBA’s from our team suggested another fix that needed to be done.
There is something called ‘Environment Variables’ in System Settings that lets programs look for the program files of that package, in a certain order. Part of the environment variables looks like the below:
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\; C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;
DTS designer installs the files in folder 80 (the second one in order). When initiated, dts designer looks in order from left to right, one path after the other, as mentioned in the environment variables. In this case, it was looking for the files in order left to right, but when it could not find anything, it just kept on giving the error message saying ‘2000 DTS designer tools need to be installed’. Though it was installed, it was not able to invoke it because of the order in which the environment variables were setup. I had to change the order to as shown below.
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\; C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;
That’s when it worked, and I am able to view the dts package now.
The order in which the system variables are setup initially was because we installed a 2000 tools after 2008. It just took the order of the installs and placed them one after the other as they were installed. Once I re-arranged them, it worked.
That’s when one of the DBA’s from our team suggested another fix that needed to be done.
There is something called ‘Environment Variables’ in System Settings that lets programs look for the program files of that package, in a certain order. Part of the environment variables looks like the below:
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\; C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;
DTS designer installs the files in folder 80 (the second one in order). When initiated, dts designer looks in order from left to right, one path after the other, as mentioned in the environment variables. In this case, it was looking for the files in order left to right, but when it could not find anything, it just kept on giving the error message saying ‘2000 DTS designer tools need to be installed’. Though it was installed, it was not able to invoke it because of the order in which the environment variables were setup. I had to change the order to as shown below.
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\; C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;
That’s when it worked, and I am able to view the dts package now.
The order in which the system variables are setup initially was because we installed a 2000 tools after 2008. It just took the order of the installs and placed them one after the other as they were installed. Once I re-arranged them, it worked.
