SSIS job package is not connecting to MS Access
I am experiencing an SQL 2014 Job Package that is not connecting to MS Access when the package is either scheduled or manually running it. The package connects and runs fine when I developed it in VBS with no errors. The package dtsx was deployed to SSIS in the Integration Services Catalog SSISB.
Connection String value: Data Source=\\xxxx\xxxx\xxxx\Da
Server Name: \\xxxx\xxxx\xxxx\Databases
UserName: admin
Password: [left blank as there is no mdw and the mdb is not password protected]
InitialCatalog: NULL
RetainSameConnection: False
These are the Connections available through the Connection Manager, once I select the OLEDB, then I selected the Microsoft Jet 4.0 OLE DB Provider. It tests and connects with VBS, just not in the SSIS Server Agent Jobs.
Connection String value: Data Source=\\xxxx\xxxx\xxxx\Da
Server Name: \\xxxx\xxxx\xxxx\Databases
UserName: admin
Password: [left blank as there is no mdw and the mdb is not password protected]
InitialCatalog: NULL
RetainSameConnection: False
These are the Connections available through the Connection Manager, once I select the OLEDB, then I selected the Microsoft Jet 4.0 OLE DB Provider. It tests and connects with VBS, just not in the SSIS Server Agent Jobs.
John_Vidmar
You have not provided any error message, but I suspect a permissions problem. The service-account that invokes the SQL-Agent-Job probably does not have access to the folder containing the Microsoft Access database file.
ASKER
The follow error message was received:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message
05/03/2019 16:30:54,pkg_Import_Appl_G
The Job was invoked by User xxxx\NetAdmin.
The last step to run was step 1 (pkg_Appliance).,00:00:04,
05/03/2019 16:30:54,pkg_Import_Appl_G
Microsoft (R) SQL Server Execute Package Utility Version 12.0.6024.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 4:30:54 PM Package execution on IS Server failed. Execution ID: 219<c/> Execution Status:4.
To view the details for the execution<c/> right-click on the Integration Services Catalog<c/> and open the [All Executions] report Started: 4:30:54 PM Finished: 4:30:58 PM Elapsed: 3.453 seconds. The package execution failed. The step failed.,00:00:04,0,0,,,,0
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message
05/03/2019 16:30:54,pkg_Import_Appl_G
The Job was invoked by User xxxx\NetAdmin.
The last step to run was step 1 (pkg_Appliance).,00:00:04,
05/03/2019 16:30:54,pkg_Import_Appl_G
Microsoft (R) SQL Server Execute Package Utility Version 12.0.6024.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 4:30:54 PM Package execution on IS Server failed. Execution ID: 219<c/> Execution Status:4.
To view the details for the execution<c/> right-click on the Integration Services Catalog<c/> and open the [All Executions] report Started: 4:30:54 PM Finished: 4:30:58 PM Elapsed: 3.453 seconds. The package execution failed. The step failed.,00:00:04,0,0,,,,0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT!!!! One very simple step. The Access is 32-bit and SQL is 64-bit, never took that into consideration. I was able to add Step-2 to the job and both jobs ran perfectly. Thank you very much for your assistance.