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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy