2

I created an SSIS to load excel files. It loops through a specified folder for relevant files and reads the data in each file into a raw data table and then I have SQL scripts that does the validation and places the data in the relevant tables etc... and it all works fine.

but I now need make the ssis package handle loading excel files with 3 different file structures. ie one file will have 50 columns, one will have 55 and one will have 60.

I have tried using a script task to load the data

Insert into <rawdatatable> select * from openrowset('Microsoft.Jet.OLEDB.4.0','excel 8.0; database=D:\SSIS\FileToLoad.xlsx', 'Select * from [Sheet1$]')

but I keep getting the error below, but adding error logging doesn't give any other errors

Exception has been thrown by the target of an invocation

enter image description here

I am using SQL Server 2014 and VS 2013

I'm not really sure what I am doing here, any help or guidance would be appreciated

Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
Noelle
  • 772
  • 9
  • 18
  • 44
  • That error isn't telling us anything. Add error handling to your Script task, and output the actual error. All that error effectively means is "An error occured." – Thom A Jan 23 '18 at 11:37
  • 2
    You are using the connection string for .XLS and not .XLSX – KeithL Jan 23 '18 at 17:54

1 Answers1

1

You must use Microsoft.ACE.OLEDB.12.0 provider, try following:

Insert into <rawdatatable> 
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                         'Excel 12.0;Database=D:\SSIS\FileToLoad.xlsx;HDR=YES', 
                         'SELECT * FROM [Sheet1$]')

References

Hadi
  • 36,233
  • 13
  • 65
  • 124