May 4, 2010 at 5:40 pm
Hello,
I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL db using SSIS.
The Data Import wizard / SSIS data flow task is not letting me use the .xlsb files. Is there any way this can be done?
I am using Excel 2010 /2007 files and SQL 2008
Thanks,
mcr132
June 4, 2010 at 2:04 pm
To connect to an .xlsb (or .xlsm) file, you must first set your Excel Connection Manager to point to any generic .xlsx file on your computer (create it if you have to), then go to the ExcelFilePath in the Properties window of the Excel Connection Manager and manually change the path to the location of the .xlsb file.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
March 13, 2012 at 10:49 am
Silly question but.. how do you make the connection manager point to any generic excel file?
March 13, 2012 at 11:19 am
Just select it like a normal one. to be clear, the idea is to select an existing file to start with, then go change to the one you really want. I think "random" threw you off.
March 14, 2012 at 3:23 pm
That's exactly right. The connection manager file selection window won't let you complete the action unless you select a valid Excel file. This can be any Excel xlsx file. Once it accepts this, you're free to edit the file path in the Propeties window to the file that you actually need. Excel Sources or Destinations in you Data Flow still recognize xlsm and xlsb formats even though the connection manager won't let you select them. It's a pretty silly and pointless restriction on Microsoft's part, especially when it's so easy to bypass it.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
March 15, 2012 at 3:30 pm
Aaaaah .. quite subtle... thanks everyone 🙂
May 23, 2013 at 9:14 am
I tried same thing to load xlsb file, but when i open in excel source I can't see any data. Can you help me out plz?
May 23, 2013 at 9:18 am
I am downloading xlsb file from web to a databse folder using script task. I have to use that xlsb file to populate one of our table on daily refresh and load. I have tried your technique, but no luck. I don't see any sheets or data in the excel source. I have downloaded xlsb file as xlsx and I can not able to open and use it. Right now I am downloading xlsb file as xls and then open and saving the file as xlsx.When I save the xls file as xlsx the file size dramtically 3 times increased (in my case 20 MB to 66 MB). Using OLEDB Access database connection connecting to the xlsx file and populating the table. This is cumbersome. Can you suggest me a handy one for this task? Database: SQL server 2005 OS: Microsoft windows 2008, Service Pack 2, 64 bit. ETL Tool: SSIS 2005
May 27, 2013 at 5:23 pm
I'm not sure you can do this with XLSB. XLSX and XLSM are basically the same - XML-based files. XLSB is a binary format, which is closer to the old XLS format than anything else. Simply changing XLSB to XLSX will not work. You could try changing it to XLS and try to connect to the file that way but, I suspect this won't work either. I'm afraid that you'll probably need to use a script task to programatically open the file in Excel and save it in a different format.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
May 28, 2013 at 10:03 am
Thanks Dirt. There is no MS office istalled on prod Serever. Can I still use the script task? Can you please suggest me a reference for the script task to open excel file and save it in different format.
May 29, 2013 at 12:23 pm
There is no MS office installed on prod Server. Can I still use the script task? Can you please suggest me a reference for the script task to open excel file and save it in different format.
Microsoft discourages the use of MS Excel automation on a server and does not support it. I think if you Google "Excel Converter" you will find several utilities that can be used to convert the Excel file into another format. You could either call this utility as a step in your SQL Agent job, or use the Execute Process task to fire it.
May 30, 2013 at 12:12 am
In order to do a script task, you'll definitely need Excel installed (since it has to open an Excel session in the background) so, that's not a viable option for you. I agree with Ed, that doing this isn't exactly recommended. I would definitely look into some conversion tools, as Ed suggest. With an Execute Process task you can call on any utility that uses command line arguments.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
June 3, 2013 at 9:10 am
Thanks alot Ed and Dirt! I appreciate your nod.
September 10, 2014 at 4:51 am
Hi,
Just change the data source connection string to :-.
OLD one -
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\iqcentral.helpdesk\Desktop\Personal Wash Bars 2014-08-26.xlsb;Extended Properties="Excel 4.0;HDR=YES";
Chnage/Update to :-
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\WebServices\.....xlsb;Extended Properties="EXCEL 12.0;HDR=YES";
Once you update it to excel 12.0 & change provider name,it should read the binary excel file.
Thanks,
Chinmayee
April 30, 2015 at 7:11 am
Hi all,
Could anyone help me with issues I'm having with the reverse scenario - I'm trying to create an SSIS package to write from SQL 2008 TO an Excel xlsb file? Can this be done?
I've been reading around, and the common answer seems to be to create the package to write to an xlsx file, and then simply tweak the connection string to point at the xlsb file instead.
I've tried this, and the modified connection string on my Excel Connection Manager is:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\FolderName\Filename.xlsb;Extended Properties="Excel 12.0;HDR=YES";
With Excel file path: C:\Test\FolderName\Filename.xlsb
When I execute my package, I get this error:
[Excel Destination [558]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Am I missing something simple, or just trying to do something which can't be done?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply