raw file source avoid bad data?

I want to have a data flow like this:

1. raw file source
2. validate data? or conditional split?
3. sql server destination

there is some bad data in my raw file. by bad data I mean datetimes that are out of the range that sql server can handle.

this should be simple right?What created the raw file and where did the data within it originally come from?|||a different package that does a raw extract from an oracle rear end.|||Well then the fact that its in a raw file is irrelevant. Your issue is the conversion between Oracle data types and SQL data types.

What columns does it complain about?
What is the datatype of those columns in your SSIS package?
What is the the datatype of the columns in the Oracle source?
What is the the datatype of the columns that you are trying to populate in the SQL Server destination?
What error messages do you get?

You need to provide more information than simply "by bad data I mean datetimes that are out of the range that sql server can handle."

-Jamie

|||[SQL Server Destination [31]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

they are datatime on both sides.

I was hoping there was something in the conditional split thingie but there is no ISDATE() function.

that is really all I need. I just want to skip the rows with bad dates. I can't fix the data it is just bad and there is nothing I can do about it.

it's like a 1,000 rows in millions is bad so it's not a big deal. seriously it's not a big deal.

I just want to avoid importing those rows with bad data. or better yet scrubbing the bad values to nulls.|||Seems like if you know what your bad dates look like (or you know what a good date looks like, which you should since BOL states what is valid for different types in SQL Server) then you could use a conditional split or a script component to filter the dates out. Obviously for the conditional split, it wouldn't be as simple as if there was an ISDATE method but it could be done with DATEPART comparisons against the know bad (or good) values.

Matt|||

datepart would ***-u-me a valid date be passed?

I will have to investigate the script option.

The implementation of the conditional split transformation seems pretty weak. Why not provide access to the entire managed runtime in the condition part?

Expressions seem like an afterthought. I would expect a lot more.

I'm just missing something here this should be easy and I'm making it way to hard.

|||Hi,
I'm puzzled by your sceptism of the expression language. Does it not do what you need?

Access to the entire managed runtime (I presume you mean the dotnet framework) is provided through the script component (which gives you regular expressions et al). As such, is there really a need to do that through the Derived Column Component as well?

Interested to hear your thoughts.

-Jamie|||

I'm reading the help on expressions. It does not seem like it will do what I need; and forcing me to use vb gives me facial ticks. I just don't like vb. I know a lot of people live and die by it but it's not for me. I’ll use it if I have to but I avoid it if I can.

why not make expressions work like they do in reporting services? you can do almost anything. In-line without writing a custom script thingies (even though it's vb based)

why invent yet another limited set of functions/conditions?

I think it's just a learning curve thing for me and eventually I figure out what I want to do.

all I want to do is write code that does

if ( isdate(mydate))
select mydate
else
select null

that's it; seems simple but now I have to learn the in's and out's of yet another vb based subsystem to make it happen with a script component.

I’m really sure I’m missing something really basic here and the light bulb will go on and I’ll say “cool I can just do it this way”.

|||There is no IS_DATE() function. I agree that would be handy.

IF...ELSE... can be provided using the conditional operator

-Jamie|||

so debugging script components does not work?

nice.

all the time I have wasted with integration services already.

I could have hand written a console c# app to to the same thing I was trying to do with integrations services.

I think I will wait for integration services 2.0 by that time it might be actually usefull.