Skip to content
+1-888-319-3663

COMMUNITY FORUM

SmartConnect Map Issue

labakermt asked 8 years ago
Hi,

I am trying to use a pre-map task on a SmartConnect map that will import data from an Excel SQL Server linked server into a custom SQL table that the map will then query to integrate the data. However the task fails and then the map fails.

The linked server is named ExcelSourcePrepaid and I can query the linked server as well as run the following to import the data into my custom table:

INSERT INTO SAF..PrepaidLoader select * from ExcelSourcePrepaid…[sheet1$]

I have to calculate and manipulate the data from the Excel spreadsheet to get it in a manner that I can integrate into GL general transactions.

The error that I am receiving on the map is as follows:

Task IMPORTDATAFROMEXCEL failed. Map run will end reporting failure. Cannot initialize the data source object OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelSourcePrepaid."

Any help would be greatly appreciated!!!

Thanks,

Lori
Answers
Best Answer
Chris Dew answered 8 years ago
Lori,

I had some difficulty setting up a linked server for Excel but found the following that may work better for you. Just run the following SQL statement against your SQL Server:

SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=administrator;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=c:GL;DBQ=c:GLgltrx.xlsx', 'SELECT * FROM [gltrx$]')

Make sure you change the directory and file locations and then the select statement to yours as well.

My real suggestion on this would be to just use the ODBC driver for Excel from inside of SmartConnect and then you can write the full SQL query there as well without the additional overhead.

Thanks,
Chris
 

If you would like to submit an answer or comment, please sign in to the eOne portal.