Friday, January 21, 2011
DTS Package on 64 bit SQL Server.
I was working on a server migration project and part of the migration was to move the DTS packages to the new SQL Server. Once the DTS packages were copied over, I started testing the DTS packages. The jobs which ran the DTS packages failed, the error was related to connecting to the source sql server from where the data was being copied. Did some research and found with the help of my colleague that DTS which is 32 bit is running on a 64 bit sqlserver. The DTS packages are 32 bit code and hence look for a 32 bit alias in order to resolve the server name. In order to solve the issue we had to create a 32 bit alias under SQL Native Configuration in the SQL Server Configuration Manager. There are 2 SQL Native Client Configuration available 32 bit and 64 bit, in order to solve the issue with DTS, i had to create 32 bit aliases. It was an interesting challenge i ran into and it took me a while to get the hang of the issue, at the beginning i did not have 32 bit vs 64 bit issues in mind.