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
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
Jim Horn
Knee-jerk reactions...
- 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.
- 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.
- 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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gotcha...thanks!