Robert MacLean
20 December 2007
Since the Excel source component in SSIS (2005) doesn't support the XLSX formats from Excel 2007 you kinda have to kiss that away (hopefully SSIS 2008 is better). So to load it you can use ODBC to create a system or user DSN to the XLSX file (office 2007 installs the ODBC drivers needed) and then using the data source reader component connect to the data source and viola! XLSX goodness and a fraction of annoyance.
I must admit that it seems slow though, but I am not sure if that is because I never needed to load 200000 records out of Excel before our my laptop has enter the Christmas spirit mode already.
If you have any better ideas or ways of doing this, I would love to hear about it.
Update: This may also be of help to some people, drivers for OleDB etc...
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
It is definately the size of the spreadsheet which is killing it. Made a new one with one record and it works fine.
I must admit that it seems slow though, but I am not sure if that is because I never needed to load 200000 records out of Excel before our my laptop has enter the Christmas spirit mode already.
If you have any better ideas or ways of doing this, I would love to hear about it.
Update: This may also be of help to some people, drivers for OleDB etc...
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
It is definately the size of the spreadsheet which is killing it. Made a new one with one record and it works fine.