Hello,

The question

Is it possible to ask SSIS to cast a value and return NULL in case the cast is not allowed instead of throwing an error ?

My environment

I'm using Visual Studio 2005 and Sql Server 2005 on Windows Server 2003.

The general context

Just in case you're curious, here is my use case. I have to store data coming from somewhere in a generic table (key/value structure with history) witch contains some sort of value that can be strings, numbers or dates. The structure is something like this :

table Values {
    Id int,
    Date datetime, -- for history
    Key nvarchar(50) not null,
    Value nvarchar(50),
    DateValue datetime,
    NumberValue numeric(19,9)
}  

I want to put the raw value in the Value column and try to put the same value

• in the DateValue column when i'm able to cast it to Datetime
• in the NumberValue column when i'm able to cast it to a number

Those two typed columns would make all sort of aggregation and manipulation much easier and faster later.

That's it, now you know why i'm asking this strange question.

============

Thanks in advance for your help.