June 20, 2008 at 1:35 pm
I pretty new to SSIS and I have a file that contains basic info I want to import into SQL 2005. I have created the table in SQL 2005 but when I run SSIS, it freaks out on a single field. Actually on a specific value in the field. The field is a datetimestamp field. I noticed in the data I am getting from the client that some dates come over as 0/0/0. This is how their system exports the data out to the text file and I have to work with what I get. SQL is obviously not liking this data and the error I get is....
Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "Dob" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Any ideas on how/where I can create a sort of mapping (or query) that will insert a null in the SQL table to replace the 0/0/0 date value in the file during the import process using SSIS?
June 20, 2008 at 2:40 pm
The simplest option, but maybe not the best, is to change how error rows are handled.
On the Flat File Source, select the Error Output. In the Error column for DOB change it from Fail Component to Ignore Failure. That should give you Null's for the 0/0/0 entries in the output.
Edit: Adding a Data Viewer is a good way to see what the output will look like. To add one, Edit the line that connects the source to the next component, go to Data Viewers and add a Grid.
June 23, 2008 at 7:39 am
Thanks for the info. Worked great.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply