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?