Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems with Excel source extraction Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 1:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:53 PM
Points: 115, Visits: 537
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
Post #1427615
Posted Thursday, March 7, 2013 10:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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
Post #1428127
Posted Thursday, March 7, 2013 11:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:53 PM
Points: 115, Visits: 537
My bad..the query actually contains a proper WHERE clause..i copied it wrong here
Post #1428412
Posted Friday, March 8, 2013 12:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 18, Visits: 159
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 :/
Post #1428432
Posted Friday, March 8, 2013 1:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:53 PM
Points: 115, Visits: 537
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
Post #1428437
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse