February 13, 2015 at 8:53 am
Ok.... very perplexed here.... as you may or may not know, Windows 64-bit does not support the 32-bit Office/Jet drivers (for ODBC connectivity). If you use something (BIDS/SSIS or SQL Server) to access MS Access or MS Excel, you may receive the following error:
The OLE DB provider "Microsoft.jet.OLEDB.4.0" has not been registered.
We already know there is a fix, kind of: install the “Microsoft Access Database Engine 2010 Redistributable” package on the server (either 32-bit or 64-bit – only one can be installed). But now comes the dilemma:
• The “64-bit” Redistributable package works with SQL Server imports but not BIDS/SSIS.
• The “32-bit” Redistributable package works with BIDS/SSIS but not SQL Server imports.
Here is the overall problem/what I am not understanding:
• We can get a couple of SSIS packages that run “manually” in SSIS in 32-bit mode / BIDS. "Manually" means open BIDS, run the package via the GUI (with the 32-bit OLE driver installed).
• We have to delete the 32-bit OLE driver and convert to the 64-bit driver to get SQL Server and Windows to recognize/try to process the SSIS package via a CMD window, i.e.,
"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec.EXE" /FILE "D:\SQLData\SSIS\MySSISPkg\MySSISPkg (1).dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
• We can run this package "manually" (either via the CMD line or via a "batch" script), but it will *not* run as a Scheduled Task in the Task Manager. These tasks did work fine previously in Windows 2003 Server.
QUESTION: Any ideas as to why the Task Manager will not run these? Regardless of whether we keep it as a 32-bit SSIS package or a 64-bit SSIS package, Task Manager will give us a "Incorrect function (0x800700001)" error message. Again, this is ONLY an issue when we try to run it via the Task Scheduler.
Other Details:
- Other SSIS processes (scheduled in a similar manner) which are transforming text files work fine, so we know the overall Task Scheduling process is OK.
- In the project properties of the dtsx, we set it for 32-bit and 64-bit, neither of which made a difference as a scheduled task.
- It is being run with Windows Administrator and a SQL Server "sysadmin" account.
- We tried running either the 64-bit dtexec as listed above or the 32-bit dtexec, but neither got rid of the "Incorrect function" error.
- As a side note: We had an older VB6 application which converted text files and imported them into SQL that had a similar problem, "Incorrect function", so we re-wrote the process as an SSIS project and it works fine now. [This process was written prior to us getting to know/using SSIS].
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply