August 21, 2008 at 4:05 pm
I am having problems loading data into a table:
the format of the source text file with 5 fields to be loaded is
column delimiter = |(pipe)
row delimiter = line feed
it looks simple but the problem with the data is -- some of the fields have missing data. the sample data is
date|type|ID|count|number
2008-08-21|employee|1005|23|8
2008-08-21|employee|1006|45 ---- the data in the number field is missing for this record
2008-08-21|employee|3|45 --- the data in the ID is missing
2008-08-21|employee|1008|10|4
2008-08-21|employee|1009|17
2008-08-21|employee|2|17
how can i load this kind of data using SSIS. i tried using the regular data flow task with the flat file source stage as input and the oledb stage as the destination. the problem with the source is:
if the second row is taken, after 45 it is expecting a '|' for the column delimiter and taking the date value from the next row into the count coulmn. and this results in error.
Is there any way that this data can be loaded ?
August 21, 2008 at 5:37 pm
If you are sure that the ID column is always going to be 4 characters and the Count will be less than ID then you can fiddle around and do some logic to insert the error rows.
But the first thing to do will be segment good data and bad (missing columns) and then apply special logic using business rules to bad data and try to insert it. But even after doing all that, if your not able to differentiate column then you will have to skip these records (if business logic allows you to).
The best thing if possible is to ask the team generating this data to put the column delimiters even if the data is missing.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply