Prev: Subquery with Max(date)
Next: Run query on multiple databases
From: Eric Isaacs on 25 Jun 2010 15:24
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
From: robboll on 26 Jun 2010 01:22
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!
From: Bob Barrows on 26 Jun 2010 09:14
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 :-)
robboll wrote:
> 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


From: Bob Barrows on 28 Jun 2010 10:52
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


From: robboll on 28 Jun 2010 17:05
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.




First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Subquery with Max(date)
Next: Run query on multiple databases