August 13, 2012 at 1:43 am
Hi,
We have to migrate DTS packages in a 32 bit system to SSIS 2008 packages in a 64 bit system.
The upstream and downstream to the server are MS Access and excel files which continue to be in 32 bit system.
The steps followed are as follows:
1. Migrate all the DTS packages to SSIS packages 1 by 1.
2.Added to a project using Add Existing
3.Build the Solution
4.Deployed the packages to a file system using Manifest file.
5.Scheduled the packages using Job activity monitor
Now none of the jobs are working and failing with an error stating unable to convert between Unicode and non Unicode data type.
We have tested the connection and that works fine,
Drivers are installed in the server.
Seems like an issue with 32 bit and 64 bit compatibility.
Can anybody help regarding it?
Tried the following solution and got the following error:
A) Modify the job step of the failing package, change the "Type" to Opearting System (cmdExec) and edit the command line manually to run it through the 32-BIT DTExec.exe. You need to prefix the full path of 32-BIT DTExec.exe before the command (Typically, under default installation, the path would be (C:\Program Files (x86)\Microsoft Sql Server\DTS\Binn\DTExec.exe).
B) Change the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\Setup\DTSPath” to point to the 32-BIT DTExec.exe. This way it would invoke all the jobs through this 32-BIT DTExec.exe.
Note: The method A) applies to a specific job while method B) would apply to all the jobs deployed in Sql Server. In SQL Server 2008, there is an option in the job step properties page to use 32-bit instead of 64-bit. Under 2005, though, the only way to control it is to use a CmdExec step to call the 32-bit version of DTEXEC.
Executed as user: <UserName> The process could not be created for step 1 of job 0xCADDFB1BF4FCB340A6DA40D6C9F10EFC (reason: 2). The step failed.
Can anybody please help?
August 13, 2012 at 10:22 am
unable to convert between Unicode and non Unicode data type
I generally find this issue when I am dealing with excel where some of the columns in the database are in Unicode format and some are in non-unicode format.
I used data conversion in my package and is working fine.
I hope it helps you a little bit.
Thanks,
Ram
August 13, 2012 at 1:08 pm
Yes, this is a common 64 bit issue, because DTS was only 32 bit. You can right-click on the project and go to properties. Select Debugging and set the Run64Runtime to False.
You can also convert your packages automatically with DTS xChange as well.
Brian Knight
Free SQL Server Training Webinars
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply