Link to home
Start Free TrialLog in
Avatar of blossompark
blossomparkFlag for Ireland

asked on

SSIS 2008 package missing rows in import to database table

I am trying to import data from an excel xlsm file into a SQL Server table using SSIS.
I want to skip the first 5 rows on the tab that I am importing  (the column names are on row 6) and only import columns A to AJ inclusive.
In my OLE DB Source properties  I set the OpenRowset value to tabname$A5:AJ.
I then set the OLE DB Destination to dynamically create a table in the target database.
This worked fine, a table was created in the target database with the required columns.
However there is  data on row 7 of the source file  which was ignored and the table is empty.
Any help appreciated. Thanks
Avatar of blossompark
blossompark
Flag of Ireland image

ASKER

when i click the preview button in BIDs on the OLE DB Source I get the following message
User generated image
Ok...I am now using SQl Server Import wizard.
The data source is an xlsm file and i am using the  Microsoft Office  10.0 Access Database Engine OLE DB Provider to connecet to this file from within the wizard.
This connects fine, the test connection succeeded.

I then use the option "Write a query to specify the data to transfer".


Using the following query , I get back the data i want beginning at the row I want (row 5 has the column names)
SELECT * FROM `tabname$A5:AJ`


I now want to limit the result set to return rows only where column Number contents are not null
SELECT * FROM `tabname$A5:AJ`
WHERE 'Number'  is not null

This statement parses ok and the Preview Data window returns a dataset but it does not filter out any rows.

The Number column is of data type float.

I then changed the query to
SELECT * FROM `tabname$A5:AJ`
WHERE 'Number' = 1

as i know there is a value 1  in the column.
This parses ok
but i get an error returned when i try to preview (see below)

===================================

The preview data could not be retrieved. (SQL Server Import and Export Wizard)

===================================

Data type mismatch in criteria expression. (Microsoft Access Database Engine)

------------------------------
Program Location:

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
   at Microsoft.SqlServer.Dts.DtsWizard.Preview.LoadPreview()
tried this also
SELECT * FROM `Properties$A5:AJ`
WHERE ROUND ( 'Number',0) =1

parses ok, but data type mismatch
now trying to convert the column to datatype integer but not parsing
SELECT * FROM `Properties$A5:AJ`
WHERE CAST ( 'Number' as INT) = 1
ASKER CERTIFIED SOLUTION
Avatar of blossompark
blossompark
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial