How to prevent CAST errors on SSIS ?

Question

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.

Answer 1

Tru using a conditional split and have the records where the data is a date go along one path and the other go along a different path where they are updated to nullbefore being inserted.

Answer 2

You could also try a Derived Column component and test the value of the potential date/number field or simply cast it and redirect any errors as being the NULL values for these two fields.

(1) If you just simply cast the field every time with a statement like this in the Derived Column component: (DT_DATE)[MYPOTENTIALDATE] - you can redirect the rows that fail this cast and manipulate the data from there.

OR

(2) You can do something like this in the Derived Column component: ISNULL([MYPOTENTIALDATE]) ? '2099-01-01' : (DT_DATE)[MYPOTENTIALDATE]. I generally send through '2099-01-01' when a date is NULL rather than messing with NULL (works better with Cubes, etc).

Of course (2) won't work if the [MYPOTENTIALDATE] field comes through as other things other than a DATETIME or NULL, i.e., sometimes it is a word like "hello".

Those are the options I would explore, good luck!

Answer 3

In dealing with this same sort of thing I found the error handling in SSIS was not specific enough. My approach has been to actually create an errors table, and query a source table where the data is stored as varchar, and log errors to the error table with something like the below. I have one of the below statements for each column, because it was important for me to know which column failed. Then after I log all errors, I do a INSERT where I select those records in SomeInfo that do not have an errors. In your case you could do more advanced things based on the ColumnName in the errors table to insert default values.

INSERT INTO SomeInfoErrors
           ([SomeInfoId]
           ,[ColumnName]
           ,[Message]
           ,FailedValue)    
    SELECT
        SomeInfoId,
        'PeriodStartDate',
        'PeriodStartDate must be in the format MM/DD/YYYY',
                PeriodStartDate
    FROM
        SomeInfo
    WHERE 
        ISDATE(PeriodStartDate) = 0 AND [PeriodStartDate] IS NOT  NULL;