date field problem - "Value could not be converted because of a potential loss of data"

Hi,

I have a flat file that has a date column where the date fields look like 20070626, for example. No quotes.

The problem is that several of the date values are missing, and instead of the date value the field looks like this , ,

That is, there are several blank spaces where the date should be. The number of blank spaces between the commas doesn't appear to be a set number (and it could even be 8 blank spaces, I don't know, in which case I don't know if checking for the Len will produce the correct results, but that's another issue...)

So, similar to the numeric field blanks problem, I wrote a script to convert the field to null. This is the logic I used:

IfNot Len(Row.TradeDate) = 8 Then

Row.TradeDate_IsNull = True

EndIf

The next step in my data flow after the script is a derived column where I convert TradeDate from 20070625 to 06/25/2007. So the exact error message I am receiving is this:

[OLE DB Destination [547]] Error: There was an error with input column "TradeDate - derived" (645) on input "OLE DB Destination Input" (560). The column status returned was: "The value could not be converted because of a potential loss of data.".

Do I need to add a conditional split after the script and BEFORE the derived column to redirect bad rows so they don't go to the derived column?

What am I doing wrong here?

Thanks

Actually, I realize I don't want a conditional split because I don't want to throw the whole row away.

I just need to fix this "data conversion" problem.

|||Why not use a derived column to work with the date field and use the trim() function to get rid of the spaces, however many there may be? I see no need to invoke a script for this.

ISNULL(TRIM([TradeDate])) || [TradeDate] == "" || LEN([TradeDate]) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))|||

Hi,

I see your logic. I removed the script part and updated the derived column with your expression.

Now I get these errors:

[Derived Column [111]] Error: The conditional operation failed.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

In the flat file conn mgr, TradeDate is defined as a DT_STR, 8. I see that you are casting TradeDate to a DT_DBTIMESTAMP. I don't think that has anything to do with this error, but thought I would mention it.

I really don't know what the problem is here!

Thanks

|||I see I forgot a couple of TRIM() calls. Try this:

ISNULL(TRIM([TradeDate])) || TRIM([TradeDate]) == "" || LEN(TRIM([TradeDate])) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))

The output of the above is a datetime field. If you don't want that, and instead want it as a string, then just get rid of the (DT_DBTIMESTAMP) cast before the substrings.|||

Hi,

Why is it when I remove the (DT_DBTIMESTAMP) from NULL(DT_DBTIMESTAMP) the expression is bad?

I guess I don't understand this part: NULL(DT_DBTIMESTAMP)

Thanks

|||Nulls have to by typed accordingly. So that is outputting a NULL of type, DT_DBTIMESTAMP. If you want it to be a string, you'll need to do: NULL(DT_STR,10,1252)|||

That's what I figured, but of course I didn't know the syntax for casting to a string.

Casting to a DT_DBDATETIME is fine since that's what it ultimately is in the table.

Anyhow, it works now, thanks!

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

|||

sadie519590 wrote:

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

Up to you!