SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with Excel source extraction


Problems with Excel source extraction

Author
Message
rockstar283
rockstar283
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 669
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
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2567 Visits: 907
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
rockstar283
rockstar283
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 669
My bad..the query actually contains a proper WHERE clause..i copied it wrong here
Kulgan_
Kulgan_
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 269
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 :/
rockstar283
rockstar283
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 669
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
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