Importing data from files is common. Csv file formats are also common but can cause issues with characters such as commas. Excel files offer a nice solution as they are usually formatted properly and do not have issues with characters. However, there can also be a common problem scheduling a job to load the data due to a jet driver issue.
ImportData:Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.
Here is how to overcome this error. First, install the ‘Microsoft Access Database Engine 2010 Redistributable‘ file which will provide the required jet driver.
Next, open the SSIS package/solution in visual studio and click on Project, <project name> Properties. On the Configuration Properties, Debugging tab, Set Run64BitRuntime = False. The jet driver is only 32 bit.
There is one final step and that is a configuration setting on the SQL Agent job itself. Without setting this, it will fail. Edit the job, click on Steps and edit the step. On the General tab, click on Configuration, Advanced and check the box that says 32-bit runtime.
That’s it! Execute the job and it should now finish successfully.