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
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
Microsoft ExcelSSISMicrosoft Office
Last Comment
johnnyg123
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
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
gotcha...thanks!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck