Running SSIS package through SQL server agent

0
votes

I have an SSIS package with excel connection manager. Thus, I need to run it in 32-bit environment. I configured the SQL-server job to run in 32-bit environment by doing the following:

Job properties-> Execution options-> use 32 bit runtime.

But I am getting this error:

Code: 0xC0209302 Source: Package Connection manager "Excel Connection Manager" Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2017-02-16 10:14:22.94 Code: 0xC001002B Source: Package Connection manager "Excel Connection Manager" Description: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Please suggest a way in which I can run the SSIS package through sql-server job agent. Thanks!

3
sql-serverssis
There are both 32-bit and 64-bit versions of the ACE (Excel) driver. You don't need 32-bit to import Excel files. Have you tried running the package in 64-bit mode? - Panagiotis Kanavos
Did you try installing the driver that the error message is telling you is missing? - Tab Alleman
@PanagiotisKanavos Yes, I tried running the package in 64 bit verison. It gives me similar error as stated above. The only difference in error is, it says "If the 64-bit driver is not installed, run the package in 32-bit mode". The SSIS package works fine in Visual studio but gives error in SQL server. - user7476196
That means that you haven't installed any driver on the SQL Server machine. You have to install it - Panagiotis Kanavos
@TabAlleman No I have not downloaded the driver. I found on google that running it under 32-bit environment will solve the problem. But it didn't. - user7476196

3 Answers

1
votes
Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

You need to download new data access engine, what I am guessing is you were trying to deploy a package to the server with old data access engine installed such as jet

here is the link: https://www.microsoft.com/en-us/download/details.aspx?id=13255

login to the target server, download either 32-bit or 64-bit based on your server version and installed. After that everything should be fine.

0
votes

There is a alter way. Open the excel file in Microsoft office Excel, and save it as "Excel 97-2003 Workbook". Then, use the new saved excel file in your file connection.

0
votes

Check if you have configured to run package in correct architecture. Navigate in SSDT to Project>>Properties>>Debugging>>Run64 bit Runtime>> Set True/False acc to arch of your machine and check.