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).
Resolution:
- When the package fails as a job, we can provide two resolutions:
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.
source: https://ssiserror.blogspot.com/2016/06/16class-not-registered-0x80040154.html
