July 20, 2009 at 5:04 pm
Hi all,
Pretty new to SSIS stuff and not what you'd call a db guru in general.
I've written a sql query which takes a column in a sql database stored as a float and converts it to a date string:
20090720 would be 07/20/2009
here's my sql (it's ugly):
CAST(Substring(Cast(cast([origdate] AS bigint) AS char), 5, 2) +'/' +Substring(cast(cast([origdate] as bigint) AS char), 7, 2) +'/' +LEFT(cast(cast([origdate] AS bigint) AS char), 4) AS smallDATEtime)
This works when i run it as a query.
I've tried putting this into a derived column transformation for that column and it keeps failing. Here's the error:
Error at Data Flow Task 2 [Derived Column [132]]: Attempt to parse the expression "CAST(Substring(Cast(cast([origdate] AS bigint) AS char), 5, 2) +'/' +Substring(cast(cast([origdate] as bigint) AS char), 7, 2) +'/' +LEFT(cast(cast([origdate] AS bigint) AS char), 4) AS smallDATEtime) " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
Error at Data Flow Task 2 [Derived Column [132]]: Cannot parse the expression "CAST(Substring(Cast(cast([origdate] AS bigint) AS char), 5, 2) +'/' +Substring(cast(cast([origdate] as bigint) AS char), 7, 2) +'/' +LEFT(cast(cast([origdate] AS bigint) AS char), 4) AS smallDATEtime) ". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task 2 [Derived Column [132]]: The expression "CAST(Substring(Cast(cast([origdate] AS bigint) AS char), 5, 2) +'/' +Substring(cast(cast([origdate] as bigint) AS char), 7, 2) +'/' +LEFT(cast(cast([origdate] AS bigint) AS char), 4) AS smallDATEtime) " on "output column "Derived Column 1" (198)" is not valid.
Error at Data Flow Task 2 [Derived Column [132]]: Failed to set property "Expression" on "output column "Derived Column 1" (198)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
Can anyone tell me the correct way of doing this? I've also tried replacing the single quotes with double quotes.
thanks
July 20, 2009 at 5:19 pm
Expression in SSIS work bit differently. Try the followin
RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", [origdate]) ,2) + "/" +
RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", [origdate]) ,2) + "/"
(DT_WSTR, 10) DATEPART( "yyyy", [origdate])
The exression ove is using datepart function to get each part and building it to desired format and (DT_WSTR, 10) is just typcasting to string.
HTH
July 20, 2009 at 5:40 pm
thank you for the reply. I tried your code (and after i figured out column names are apparently case sensitive) i got the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task 2 [Derived Column [132]]: Attempt to parse the expression "RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", [origDate]) ,2) + "/" +RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", [origDate]) ,2) + "/"(DT_WSTR, 10) DATEPART( "yyyy", [origDate]) " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
Error at Data Flow Task 2 [Derived Column [132]]: Cannot parse the expression "RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", [origDate]) ,2) + "/" +RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", [origDate]) ,2) + "/"(DT_WSTR, 10) DATEPART( "yyyy", [origDate]) ". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task 2 [Derived Column [132]]: The expression "RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", [origDate]) ,2) + "/" +RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", [origDate]) ,2) + "/"(DT_WSTR, 10) DATEPART( "yyyy", [origDate]) " on "output column "Derived Column 1" (231)" is not valid.
Error at Data Flow Task 2 [Derived Column [132]]: Failed to set property "Expression" on "output column "Derived Column 1" (231)".
I'm not sure i understand what your code is doing, but just so i'm clear ...
origDate is a FLOAT in another table. It's in the format of 20090720 - YYYYMMDD. From your code, assuming i understand it right, it looks like you are trying to use the datepart function to extract the MM, DD and YYYY value. Will that work even though this is stored as a float value?
thanks again
July 20, 2009 at 6:45 pm
Sorry I didn;t read properly that the datatype of origDate is float. You may try this
SUBSTRING((DT_WSTR,8)[origDate],5,2) + "/" + SUBSTRING((DT_WSTR,8)[origDate],7,2) + "/" + SUBSTRING((DT_WSTR,8)[origDate],1,4)
This expression first casts origDate into string and performs substring on it.
HTH
Mukti
July 21, 2009 at 10:49 am
thank you. It looks like that works - i haven't actually run the code yet but i dont get an error when i insert it now.
Is there a good online source that explains all the conversion functions like dt_wstr ?
thanks again for your help
July 21, 2009 at 3:24 pm
July 21, 2009 at 3:30 pm
ok, thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply