March 6, 2013 at 1:37 pm
I have an excel file with sheet containing 210000 records..I am trying to extract this file into a SQL table.
In SSIS, I am using a Excel source and a SQL server destination adapters. When I am running the package, SSIS is only extracting 4500 records into the SQL table.
If I use a SQL query "SELECT * FROM [SHEET1$] WHERE ID>4590" instead of a sheet name, then no records show in the Preview mode.. Don't know whats going wrong here..
Can someone please explain me what am I doing wrong..Thanks in advance
March 7, 2013 at 10:34 am
That looks like a badly formed query. You are missing a 'WHERE' and a field/column before the comparator.
Excel is definately the poor relation when it comes to reading and writing data in SSIS. Do you have an alternative data source. Even CSV in a flat file connection is easier to work with than Excel.
Generally you will have a nightmare with the differences between xls and xlsx/xlsm formats and whether you are reading into a 32bit SQL environment or 64bit environment.
Obiron
March 7, 2013 at 11:28 pm
My bad..the query actually contains a proper WHERE clause..i copied it wrong here
March 8, 2013 at 12:42 am
Check first few records (around 20)... if they are numbers and later (after 4500 rec.) in this columns are strings/dates etc.. (different then this few first records) then SSIS will truncate them with no warning :/
March 8, 2013 at 1:07 am
But in the advance editor of the Excel source adapter I have mentioned Unicode String as the data type for this field..so don't think that can be a problem
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply