Dealing with BAD Dates in SSIS

[robboll]

I know this isn't the correct group for this tool, but it belongs to
SQL Server . . . so

I have a simple table with five columns. One of the column is a
date.

When I import the data into a datetime field I get the error: An OLE
DB error has occurred. Error code: 0x80004005. Description:
"Invalid character value for cast specification".

As it turns out the error occurs when it hits a date with this value
"00/00/0000" or a bad date like "02/31/2010"

So like with DTS transformations, I need to check to see if the value
is a correct date format and if it is not, make it null before it is
appended to SQL Server.

I will appreciate any help with this one.

Thank you!

RBollinger

[Bob Barrows]

I initially thought that SSIS had an IsDate() function, but I see I was
mistaken:
http://msdn.microsoft.com/en-us/library/ms141232.aspx

Where is the data coming from? If it's a database, perhaps you can do this
check in the query that retrieves the data. Otherwise, you will have to use
a staging table and use a sql query with a CASE statement to insert the data
into the real destination table.

--
Bob Barrows

[jm27102]

I just dealt with something very similar.
Use the ISDATE() function to check if the date is valid.
Redirect your "bad" rows to another table, and create a TSQL function
to process, convert or correct the bad dates.

"robboll" wrote:

> .
>

[jm27102]

IsDate() should be in SQL Server in "Other Functions" it returns a 1 or a 0

"Bob Barrows" wrote:

> .
>

[Bob Barrows]

Yes, I know. That's a T-SQL function which is not in the list of _SSIS
Expression_ functions :-)
That's why I suggested using a staging table so that he could use the T-SQL
ISDATE() function in a CASE statement.

--
Bob Barrows

[Eric Isaacs]

I agree with Bob. Put the data into an import table, then use the
ISDATE function from there to get it into the permanent table.

-Eric Isaacs

[robboll]

Howdy Bob! Got you in Facebook! But believe this is the best place
to deal with SSIS stuff. haha

You know some parts of DTS I really miss, like being able to edit
transformations and qualifying the data before importing it -- and
without import tables! This has been a major issue with me for quite
some time since I work for the City of Dallas where we deal with a lot
of strange data formats. The OS 390 mainframe data dump that I am
dealing with now has some dates that use 00/00/0000 like a null date
and that screws everything up at import time.

Just yesterday I found something that hopefully deals with this very
issue: http://www.sqlis.com/post/Regular-Expression-Transformation.aspx.

It's a free add-in and I installed it in about 3 mins. It adds a data
flow transformation called "Regular Expression" Interestingly enough
it has some help notes that deal with my specific issue and what you
suggested about using as isdate() like function:

(Date_To_Check == "00/00/00" || (DT_Date)Date_To_Check <
(DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_To_Check

The source of this is: http://www.sqlis.com/sqlis/post/Expression-Date-Functions.aspx

Maybe you can help me figure it out ;--{ ) And add a new screwdriver
to your assortment too! When I run it all tasks turn green like I
just won the jackpot, but no data gets appended. So I'm leaving
somethin out. I'll continue to play with it and get it to work.
Looks promising!

[Bob Barrows]

That's a new one for me - thanks for pointing it out. From reading the
descriptions, it appears you really need to use the RegexClean
transformation:
http://www.sqlis.com/post/RegexClean-Transformation.aspx

"Use the power of regular expressions to cleanse your data right there
inside the Data Flow. "

I won't be able to play with these until sometime next week, so do let us
know how you get on ... and post the regular expression you're using :-)

--
Bob Barrows

[Bob Barrows]

robboll wrote:
> Howdy Bob! Got you in Facebook! But believe this is the best place
> to deal with SSIS stuff. haha

Actually, the integrationsvcs group is still there. I'm still subscribed
to it.

> You know some parts of DTS I really miss, like being able to edit
> transformations and qualifying the data before importing it -- and
> without import tables! This has been a major issue with me for quite
> some time since I work for the City of Dallas where we deal with a lot
> of strange data formats. The OS 390 mainframe data dump that I am
> dealing with now has some dates that use 00/00/0000 like a null date
> and that screws everything up at import time.

I'm not familiar with that database, but if it supports SQL, they should
be able to create a view that transforms that date into something
usable. The view can then be the target of the dump.

>
> Just yesterday I found something that hopefully deals with this very
> issue:
> http://www.sqlis.com/post/Regular-Expression-Transformation.aspx.
>
> It's a free add-in and I installed it in about 3 mins. It adds a data
> flow transformation called "Regular Expression" Interestingly enough
> it has some help notes that deal with my specific issue and what you
> suggested about using as isdate() like function:
>
> (Date_To_Check == "00/00/00" || (DT_Date)Date_To_Check <
> (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_To_Check
>

This should fix your "null date" problem, but will not deal with the
bad-date problem. Actually, if this is fed a bad date, won't the
"(DT_Date)Date_To_Check" throw an error?

> The source of this is:
> http://www.sqlis.com/sqlis/post/Expression-Date-Functions.aspx
>
> Maybe you can help me figure it out ;--{ ) And add a new screwdriver
> to your assortment too! When I run it all tasks turn green like I
> just won the jackpot, but no data gets appended.

Exactly how did you try to use it? It should have been used in a Derived
Column task. In other words, you should have been getting a new column
in the output.

--
HTH,
Bob Barrows

[robboll]

I'm not giving up because I need a production solution now -- which to
me means DTS imported into SSIS (what a shame). Hey! It works!!!

The simple solution in DTS is to comment out the bad imput row in the
Transformations:
'DTSDestination("CHANGE_DATE") = DTSSource("Col004")

And use the IsDate function as you mentioned earlier in a simple IF/
Else statement:

If IsDate(DTSSource("Col004")) then
DTSDestination("CHANGE_DATE") = DTSSource("Col004")
else
DTSDestination("CHANGE_DATE") = null
end if

I was hoping to be able to use a script task to do this when I found
what I thought was the solution on SQLIS.com. Looked promising at the
time but still no luck. I know to use import tables but SSIS is
supposed to be an improvement to DTS. So I am determined though to
find the SSIS solution. Will keep digging.

[Bob Barrows]

I'm still not entirely sure what you tried. Did you use the Derived
Column task to create a new column using the expression you referred to
in the earlier post? That should certainly handle the 00/00/0000
problem. What it won't handle is the invalid dates problem and the regex
solution seems to be the right path for that.
Again, I'm not entirely sure what you have tried, but I do suggest you
use the message board at the SQLIS site to ask for help with the tools
they provide.

--
HTH,
Bob Barrows