Showing posts with label cast. Show all posts
Showing posts with label cast. Show all posts

Monday, March 19, 2012

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:

I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html

Sunday, February 12, 2012

Concatenation of Text

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?Yes, UpdateText processes only one row per call.

The MDAC infrastructure was never meant to handle BLOBs, so trying to update a TEXT column in a million rows at once is really a bad idea. I'd either change the column datatype, or find a different way to acheive the same goal.

This just sounds like a problem sniffing eagerly at a new victim to me!

-PatP|||Thanks Pat. Unfortunately, I have to use a TEXT column because the data can exceed the 8k limit for VARCHAR and CHAR. I guess my choices are to use DTS and some sort of ActiveX script, write a script to concatenate before it hits the database, or do the unthinkable and write a cursor (although I don't really want to do that).

Dandy|||Nothing quite like being caught between the devil and the deep blue sea, is there? Given those choices, I'd opt for DTS.

Actually, cursors aren't logically bad, it is just that their performance is awful compared to set operations. Many databases like Oracle and Z-Series DB2 rely on cursors to do much of anything.

Be forewarned though, TEXT manipulation is slower than other datatypes. Just retrieving or storing a row with a TEXT or IMAGE column takes a lot longer than it does without those columns.

-PatP|||Of course it does, doh...First it needs to get the pointer (binary(16)), then retrieve 8K worth of data stored in a separate set of pages which results in at least 1 additional IO per row retrieved. BLOBs are wonderful (hehehe) when used for what they were invented, - not for set-based operations. And this is one of the few cases when a cursor may very well be applicable.

The MDAC infrastructure was never meant to handle BLOBs...Really? Who told you that?|||Really? Who told you that?It was either the guy who invented DTS, or the GPM for MS-SQL 7.0. They were both there, I just don't remember who actually said it and who just nodded sagely.

-PatP|||Oh, I see, and I'm an airplane :D|||I'm a teapot! I'm a teapot!