I have some SSIS package that I am trying to run using SQL Server Agent Job. The package gets executed without any issues when run from Visual Studio. But when I create a job and run in the SQL Server Agent it gets failed with the below error.
I have created a SSIS Proxy and SSIS Credential using my Windows Account. I am running this job using that Proxy Account in the SQL Server Agent. The Package is located in the D Drive and it has full access for my windows account. However I am not able to understand why this error occurs.
I searched in the google and found the below link regarding this issue.
However there are no resolution steps provided. Can anyone help me out to resolve this issue? Is this due to access control?
Thanks in Advance.
This is a very common error when a package is deployed in a 64-BIT environment and the package is using Microsoft Providers that is not available in 64-BIT (e.g. Microsoft JET provider and Microsoft provider for Oracle).
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.
There would be scenarios where this error is encountered when a package is executed through a .NET program using the SSIS APIs(using LoadFromSqlServer(), Execute()). In that scenario, we need to change the Target Platform to “x86” from the Project Properties.
There might be scenarios where the providers used in the connection managers would be missing from the System where the package is run. In that case, the execution of the package would fail BOTH from BIDS as well as from Sql and the resolution would be install the appropriate provider.
User contributions licensed under CC BY-SA 3.0