Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Import Excel binary files into SQL Db using SSIS Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 12:23 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 30, 2015 7:08 AM
Points: 491, Visits: 1,296
[b]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.



Post #1457912
Posted Thursday, May 30, 2013 12:12 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 22, 2015 4:04 PM
Points: 42, Visits: 133
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
Post #1458047
Posted Monday, June 3, 2013 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 8:38 AM
Points: 7, Visits: 278
Thanks alot Ed and Dirt! I appreciate your nod.
Post #1459346
Posted Wednesday, September 10, 2014 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:02 AM
Points: 1, Visits: 23
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 [color=red]12.0[/color];HDR=YES";

Once you update it to excel 12.0 & change provider name,it should read the binary excel file.

Thanks,
Chinmayee
Post #1612135
Posted Thursday, April 30, 2015 7:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 23, 2015 7:30 AM
Points: 32, Visits: 180
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?
Post #1681602
Posted Friday, May 22, 2015 4:12 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 22, 2015 4:04 PM
Points: 42, Visits: 133
GloriousRuins (4/30/2015)Could anyone help me with issues I'm having with the reverse scenario


That's exactly how you should do it so, the issue that you're having may be related to something else. If all else fails, try the following so that it only evaluates the file name at run-time:

1) Copy your xlsb to an xlsx file. Keeping the path and name the same isn't necessary but, it just makes it easier - the goal is just to have something with the same exact contents.
2) Repoint your connection there and ensure that everything works.
3) Create a variable with the FilePath or ServerName (doesn't matter which one) of your Excel file connection (stick to the xlsx file for now to ensure this works correctly).
4) Use an expression within the Excel connection manager's FilePath or ServerName to point at your variable. Run and test to make sure this works. If not, turn off validation on the data flow task and anything else that you can think of.
5) Once you get 5 to work, Change your variable to point at the xlsb and run again.

Let me know if this works.


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #1688230
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse