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 06, 2013 1:37 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:54 PM
Points: 105, Visits: 474
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 07, 2013 10:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
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 07, 2013 11:28 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:54 PM
Points: 105, Visits: 474
My bad..the query actually contains a proper WHERE clause..i copied it wrong here
Post #1428412
Posted Friday, March 08, 2013 12:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 12:52 PM
Points: 17, Visits: 133
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 08, 2013 1:07 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:54 PM
Points: 105, Visits: 474
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