We help IT Professionals succeed at work.

write excel invalid data to sql server table in ssis

johnnyg123
johnnyg123 asked
on
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
Comment
Watch Question

View Solution Only

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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. 
johnnyg123

Author

Commented:
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
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Look at your SSIS data flow mapping.  When you first connect the arrows between source and target it will ass-u-me the source column data types based on reading the first couple of values.  You'll have to override those source column data types to varchar's. 
johnnyg123

Author

Commented:
gotcha...thanks!