August 18, 2009 at 1:52 pm
I receive a txt file from mainframe and need to insert it in a table from Sql Server.
In TXT file my first column is date on YYYYDDMM format, but in the table my first column is a smalldatetime. Before i insert the data i need to check if my first column is a valid date.
For example:
20091408 -> ok -> 2009-14-08 00:00:00
00000000 -> not ok, is not a valid date
Witch SSIS component can I use to validate the data before insert in the table?
Regards,
Fernando
August 18, 2009 at 2:23 pm
I suppose that depends on what you want to do with the invalid rows. If you have 10 rows in your file and 2 have invalid dates, what do you want to do with the good rows? What about the bad rows?
August 19, 2009 at 5:56 am
John Rowan (8/18/2009)
I suppose that depends on what you want to do with the invalid rows. If you have 10 rows in your file and 2 have invalid dates, what do you want to do with the good rows? What about the bad rows?
The good rows go into the table, and the bad rows too but with a default value.
If datetime(0001-01-01 00:00:00) if number(-7) if character (*) ...
Regards,
Fernando
August 19, 2009 at 9:23 am
Then I would use a Derived Column transformation in your data flow to replace the existing values with the defaults when they come into the data flow as invalid.
You'll want to use an expression to check for the invalid value and conditionally replace it with the default value.
So if you are seeing 00000000 in your source file in a date column, what data type are you bringing that column into your data flow as?
August 19, 2009 at 11:12 am
John Rowan (8/19/2009)
Then I would use a Derived Column transformation in your data flow to replace the existing values with the defaults when they come into the data flow as invalid.You'll want to use an expression to check for the invalid value and conditionally replace it with the default value.
So if you are seeing 00000000 in your source file in a date column, what data type are you bringing that column into your data flow as?
Ok, but what expression in Derived Column can i use check the data type?
If I receive 00000000 i need to load '0001-01-01 00:00:00'.
Regards,
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply