Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

write excel invalid data to sql server table in ssis

I have ssis package that reads in excel file

I know there are some rows in the excel file that have non date values in cells that should have date values

I tried redirecting rows to an error table that has columns that are defined as varchar(255)

However package keeps failing saying conversion error

I know there is a conversion error just want the records to write to a table

not sure what I am missing
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Knee-jerk reactions...
  1. Excel is great in that users can do thousands of different customizations, but that greatness makes it terrible for a data source to be imported into a normalized database that requires a rock-solid 'contract' between source and target. 
  2. HIGHLY recommend in your data pump create a new talbe (We call these 'staging tables').  Make some or all of the columns (n)varchar's.  This way the SSIS package has a much higher chance of succeeding without error when data is not the expected data type.
  3. Then in T-SQL you can write code in a SP to validate that dates are dates, numbers are numbers, etc., and then (a)  Move all of the good rows into the ultimate target table, and (b) handle the validation fails gracefully. 
Avatar of johnnyg123


Hi Jim

I do have excel going to a table that has columns defined as nvarchar(255)

I put imex =1 in to the excel connection thinking that would force excel to read in as string

however, when I look at the excel columns in ssis it thinks it is date and I think this is issue when the value is in fact not a date

Not sure what I am missing
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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