SSIS/SSA Job Error: Microsoft OLE DB Service Components - Class not registered
I have an SSIS package that uses an Access database as a data source. When I run the package manually through the solution, it runs fine. But when I run it through the SSA job (SS 2005), I am getting this error (running as SSIS job type).
My understanding is that SSIS packages are defaulted to a 64-bit run time and DTS 2000 packages are defaulted to 32-bit. And since I need to use MS Access as a data source, I'll need to run at 32-bit. Is that correct?
We have a legacy DTS 2000 package set up right now that runs this package through an SSA job (we want to switch over to run it through an SSIS package). I checked out the configs of this DTS package and found very few differences from the one that I set up in SSIS, however, there is a setting in the DTS package (properties) that I am thinking may allow it to run through the SSA job. Package properties > advanced tab > box CHECKED use OLE DB service components. Or maybe it doesn't and I'm running in circles.
So here's what I have set up and the errors I am getting...
Data source OLE DB Provider: "Microsoft Office 12.0 Access Database Engine OLE DB Provider"
Run64BitRunTime = False (solution config)
In other EE forums, I've seen a possible solution listed as using a t-SQL command to run the job (instead of the SSIS job type) in addition to changing the Run64BitRunTime config in the solution.
I can't however seem to find the path that is recommended (in other EE solutions) for the DTExec.exe file.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.
Here's what I found on the server that runs the SSIS packages (I don't know whether it's a 64 or 32 bit exe):
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
Here's the command line I'm using:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
/FILE "<my package path>" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
SSA job error: "Incorrect syntax near 'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
One last note, I've been doing some research and also found information about MDAC components needing to be installed in the location (server in our case) where the package needs to run. I'm assuming that since the current DTS 2000 package can run through the SSA job, that I shouldn't be concerned about checking/installing MDAC components. We also have several SSIS packages that use Excel connection managers as data sources and they run fine through SSA jobs (and I left the Run64BitRunTime = True).
I would REALLY appreciate any help with this! Thank you.
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error
My understanding is that SSIS packages are defaulted to a 64-bit run time and DTS 2000 packages are defaulted to 32-bit. And since I need to use MS Access as a data source, I'll need to run at 32-bit. Is that correct?
We have a legacy DTS 2000 package set up right now that runs this package through an SSA job (we want to switch over to run it through an SSIS package). I checked out the configs of this DTS package and found very few differences from the one that I set up in SSIS, however, there is a setting in the DTS package (properties) that I am thinking may allow it to run through the SSA job. Package properties > advanced tab > box CHECKED use OLE DB service components. Or maybe it doesn't and I'm running in circles.
So here's what I have set up and the errors I am getting...
Data source OLE DB Provider: "Microsoft Office 12.0 Access Database Engine OLE DB Provider"
Run64BitRunTime = False (solution config)
In other EE forums, I've seen a possible solution listed as using a t-SQL command to run the job (instead of the SSIS job type) in addition to changing the Run64BitRunTime config in the solution.
I can't however seem to find the path that is recommended (in other EE solutions) for the DTExec.exe file.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.
Here's what I found on the server that runs the SSIS packages (I don't know whether it's a 64 or 32 bit exe):
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
Here's the command line I'm using:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
/FILE "<my package path>" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
SSA job error: "Incorrect syntax near 'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
One last note, I've been doing some research and also found information about MDAC components needing to be installed in the location (server in our case) where the package needs to run. I'm assuming that since the current DTS 2000 package can run through the SSA job, that I shouldn't be concerned about checking/installing MDAC components. We also have several SSIS packages that use Excel connection managers as data sources and they run fine through SSA jobs (and I left the Run64BitRunTime = True).
I would REALLY appreciate any help with this! Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Problem solved.
You want to share with us how you solved it?
ASKER
Sure. I removed the old migration package, then completed a fresh migration of the package and was able to run this new package through the SSA job. As far as the configurations within the migrated package, I don't know exactly how the DTS conversion worked in order to run in a 32-bit run time.
Anyway, seems like SQL Server didn't install 32bits features (what's really strange).