March 4, 2008 at 2:46 pm
I'm pulling a century date field (ie: 1080131) from a DB2 table. I want to strip off the first 5 digits of that field. I'm trying to use the Derived Column in SSIS 2005 to accomplish this. This is the expression field:
SUBSTRING([RECD09], 1, 5)
However when I use Substring to strip the first 5 I get an error "SUBSTRING" does not support the data type "DT_DECIMAL". So I tried using CAST to convert the decimal to a String. I've tried using different :crazy:"Type Casts" but I'm not sure which one to use and how the syntax would look.
So can I use CAST, if so, how or is there another method that is better?
Thanks for your help, Dale
March 4, 2008 at 3:23 pm
I dont think you can use cast in SSIS packages. You should use the WSTR datatype. Like this:
SUBSTRING((DT_WSTR,10)[RECD09], 1,5)
Note: I set it at 10 to prevent an error in testing. You can set it however you like, but beware of truncation problems. It does not matter how large the DT_WSTR is because you are still only grabbing 5 characters.
March 5, 2008 at 6:53 am
Adam,
Thank you for your help. That worked great!
Where is the best place to find other examples of how to use Type Casts in SSIS 2005?
Thanks again, Dale
March 5, 2008 at 10:25 am
Here is a great link. http://msdn2.microsoft.com/en-us/library/ms141704.aspx
Note that WSTR is unicode. If you do not need unicode characters you should use (DT_STR,10,1252). Code page 1252 is the standard.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply