-1

I'm trying to use SSIS 15.0.2000.68 to import an .xlsx file to a Sql Server table. When I try to use an Excel Source in the Data Task, it can't read the spreadsheet. I manually saved the .xlsx file to an earlier version of Excel and it worked fine.

I need to upload the xlsx file daily and I can't manually do it every day. I tried using a File Task to save as an .xls but SSIS can't read that. I also tried to save as csv or txt, and SSIS can't read them either.

I've read there's a MS redistributable package (Microsoft Access Database Engine 2010 Redistributable) that will allow SSIS to import .xlsx files, but my manager doesn't want to install it.

Is it possible to use a script task to save it as another file type?

user1707389
  • 99
  • 1
  • 4
  • 13
  • So what is the earlier version of excel file you used? Prior to xlsx was xls… – Solar Mike Apr 18 '22 at 20:07
  • Yes, earlier version with .xls extension. – user1707389 Apr 18 '22 at 20:10
  • So you say it works with xls, and then you say it does not. You may need to be more clear about this… it either works or it does not. – Solar Mike Apr 18 '22 at 20:37
  • No, I said it didn't work with .xlsx. I saved it as an earlier version (a version ending with .xls) and it works. Please read carefully when commenting. – user1707389 Apr 18 '22 at 20:38
  • As for read carefully, I did. You say it does not work with xlsx. Then you say it works with an earlier version, which you don’t specify. I asked in a comment if that was xls and you agreed. Then you you state SSIS cannot read xls - the very version you say works but now does not. « Read carefully » indeed. – Solar Mike Apr 19 '22 at 03:39
  • Yes, it can read .xls - I said that. .xls is the earlier version of Excel. The issue is converting the .xlsx file to .xls, or .txt, or .csv format. – user1707389 Apr 19 '22 at 12:20

1 Answers1

0

If you are able to create an OLEDB connection inside of your SSIS package you could attempt the top answer on this question, as this sounds like it may be useful for reading an .xls file. Essentially you would instantiate a new OLEDB connection inside of your script task and point it at the Excel file path, select all the data, and read it into a DataTable. You would first want to insert the appropriate columns into your DataTable, but assuming the columns do not change this should be easy to automate on a daily schedule.

After you have the data stored in the DataTable, you could pass it into a data flow task and create another file with the data as needed.

cdbullard
  • 143
  • 2
  • 10
  • Thanks - the example in that question shows them using different drivers for .xlsx versus .xls. That's my issue. I can import .xls files (earlier version of Excel) but cannot import .xlsx file. – user1707389 Apr 19 '22 at 12:34