Agent Job Error running SSIS package importing Access database table

[sportsguy]

Hack SQLSERVER 2016 dba here, ie, not technically certified, but have enough experience to create properly designed tables, indexes, view queries, security users, schemas, procedures, and running all procedures from O365 ms Access from a button. However, I can't get a local file system SSMS created SSIS package to run from an Agent job. The job is import /copy an MS access table from an mdb file table with the same table name and structure, to the server staging table. I saved an SSIS package locally while importing a table from my staging access database to a SQL Server staging table. Manually, works fine. Step 1 truncate receiving staging table works fine, Step 2 running the SSIS package fails. I suspect with the ODBC driver link, though not sure why it works manually.


source connection Data Source=C:\Users\username\Documents\staging_tables\stgHEADCOUNT.mdb;OLE DB SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;

What am I missing, or did i set the MS access database security level wrong with the agent job? or is the OLE DB service not recognized. there is no user name and password with the local database.

Thanks in advance and to the doubters, the company employees are not allowed to access the SAP system at all, but we can run and export webi bi reports, so either a flat file or from an access database table import are my only two options.

Thanks in advance,
sportsguy

 

[Isaac]

from an access database table import are my only two options

It is really not recommended to use SSIS to connect to an Office application at all, and I would strongly recommend using a flat file instead. If you doubt my response, post your question on sqlservercentral.com and see what kind of replies you get.

Does your SSIS package Connection Manager (the one that connects to Access) have an error mark on it, or does the error ONLY happen at run time? Have you made sure to compile / build your package before running it?

Also, what actually IS the error you get when running it? You can see from the sql server job agent history

 

[sportsguy]

thanks for the reply, obviously I am a poser . The actual error message is "The requested OLE DB provider Microsoft Jet OLEDB.4.0 is not registered." so the follow on messages are all about the failures due to that issue. And yes, the error only happens at run time. Let me look at the flat file option, I haven't spent much time with the extract app to flat files. Thanks for not being offended.

sportsguy

 

[Isaac]

thanks for the reply, obviously I am a poser . The actual error message is "The requested OLE DB provider Microsoft Jet OLEDB.4.0 is not registered." so the follow on messages are all about the failures due to that issue. And yes, the error only happens at run time. Let me look at the flat file option, I haven't spent much time with the extract app to flat files. Thanks for not being offended.

sportsguy

I didn't mean it like that, I just trying to point in right direction. Having any server-only process (like SSIS), interact with Office is widely discouraged, that's all. If you can go the flat file route that will be good

 

[SQL_Hell]

Hello, the issue is that there no 64 bit JET drivers for SQL server, so once you created the job it started trying to use the drivers on the SQL server as opposed to the drivers on your machine. One option is to run your SSIS package in 32bit mode and install the access drivers on the sql server (this is quite tricky though), or export your access data to .csv /flat file (as above poster mentioned) and import to sql server that way.

Is this a one time process or something you want to schedule?

 

[sportsguy]

@pisorsiaac - LOL! no worries, I want to get the certification, I admit that I am a SQL SERVER dba poser, I just don't have time, and at the end of my career, I don't have alot of studying desires! So I cheat alot (google), but I also don't like posterswith no experience or obviously haven't tried to fix it on their own who demand solutions from volunteers. The company operating structure is very difficult to manage within to get help. . . and I only have about 12 months of a career left, depending upon my tolerance for more work, then off to retirement golf camp!

The MS Access issue is that I can write directions for an idiot to run process scripts on the server to truncate tables, process and move data around from development point of view. Not a production server at all. . . I get the difference. . . I am not just there yet at all.

@SQL_Hell, thanks! I will work the flat file route though the Hana BI webi tool to get a csv file which is workable.

sportsguy