More Problems With 32 Bit Package on x64 SQL Server

Hello,

I have gone through some documentation on how to execute a 32 bit package on SQL Server 2005 x64, and I'm still running into problems when I attempt to execute such a package from a Job.

Critical Points:

-The package was imported into an instance of Integration Services 2005 from the file system to a subdirectory of the MSDB folder. From Management Studio, Integration Services, the path to the package looks like this: Stored Packages\MSDB\CPS\CPS_Collections. 'CPS_Collections' is the name of the package.

-This package has a connection to an Access 2000 database using the Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. I understand that there is not a 64 bit version of this provider. The Access database is stored on a different machine than the one that hosts the SQL Server 2005 x64 instance.

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

-When I attempt to run the Job, the Job fails. Here is a part of the error message from the Job history:

The process could not be created for step 1 of job 0x9B318B226174A24B8BD63CE8F4814864 (reason: The system cannot find the file specified).

Does this point to a problem of where the Access datbase is located? Could it be that the account that runs SQL Agent does not have access rights to the directory? Is there a way to run an operating system command as someone other than SQL Agent? Is there something wrong with the command line that I am using?

Please share if you have any ideas on this.

Thank you for your help!

cdun2

On a 64-bit server, the SSIS job step calls the 64-bit version of DTEXEC. You need to call the 32-bit version (located in C:\Program Files (x86)\Microsoft Sql Server\90\DTS on most installs). To do this, you need to use a CmdExec job step.|||Hi,

Since the error description said : The system cannot find the file specified, maybe you set the location of Access database into a mapping drive or something, try using this format \\ip_address_of_the_machine\

Best regards,

Hery|||

cdun2 wrote:

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

You can use DTEXECUI to construct the command arguments, but you still need to supply the executable name for the command line. I.e. your full command line should look like

"C:\Program Files (x86)\Microsoft Sql Server\90\DTS\Binn\DtExec.exe" /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

|||

Thanks for the help, and sorry for the late response. I'll take your responses and look into this further. I can say that the location to the Access database is not expressed as a mapped drive.