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: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
[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: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
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: Thursday, October 9, 2014 7:27 AM
Points: 7, Visits: 260
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse