Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import Excel binary files into SQL Db using SSIS


Import Excel binary files into SQL Db using SSIS

Author
Message
Ed Zann
Ed Zann
Mr or Mrs. 500
Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)

Group: General Forum Members
Points: 505 Visits: 1391
[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.



Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
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
Make an Impact
Make an Impact
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 363
Thanks alot Ed and Dirt! I appreciate your nod.
7chins
7chins
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 28
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
GloriousRuins
GloriousRuins
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
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?
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
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
jeffstubing
jeffstubing
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
Thanks Lady. I was able to set up the Excel Connection Manager doing this, but when I go into the Excel Source task to select my columns I get a "File is not in the expected format" message. Angry Any idea how to beat this?
komal145
komal145
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 894
Can you be more precise...i did not know exactly where you changed?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search