I'm migrating a fu... dts to SSIS. That's fine. I've got a derived column task for stuff as cdbl(DTSSource("Col014") /100).
But how the source plain file have a lot of columns suddenly I find this:
Function Main()
Fecha=Right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsDate(Fecha) then
DTSDestination("FechaOp") =Fecha
Else
DTSDestination("FechaOp")=null
End IF
Main = DTSTransformStat_OK
End Function
So that 'Derived Column' is useless at all for to encompass this rule unless to anyone be able to tell me how to implement that conditional snippet inside...
I'll be to use a Script Component Task only for that dammed column when the rest ones already defined.
!!!!
Do y'know hare and tortoise tale? So here is the same but in reverse order. jezz|||There is a conditional operator in the SSIS expression language. It will do what you need to do.
Here's the BOL topic on it: http://msdn2.microsoft.com/en-us/library/ms141680(SQL.90).aspx
-Jamie
|||Thanks Jamie. hurries doesn't provide us good advices..|||OK.
I've taken as example this and then I've converted into this line:
'Fecha = Right(DTSSource("Col014"), 2) & "-" & Mid(DTSSource("Col014"), 5, 2) & "-" & Left (DTSSource("Col014"), 4)
'If IsDate(Fecha) Then
' = Fecha
'Else
'DTSDestination("FechaOp") = null
'End If
Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing
But fails:
Error at CargaModelo187 [Derived Column [10345]]: Attempt to find the input column named "Column13" failed with error code 0xC0010009. The input column specified was not found in the input column collection.
Error at CargaModelo187 [Derived Column [10345]]: Attempt to parse the expression "Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
Error at CargaModelo187 [Derived Column [10345]]: Cannot parse the expression "Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing". The expression was not valid, or there is an out-of-memory error.
Error at CargaModelo187 [Derived Column [10345]]: The expression "Isdate(Right([Column 13],2) & "/" & M
id([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing" on "input column "Column 13" (11693)" is not valid.
Error at CargaModelo187 [Derived Column [10345]]: Failed to set property "Expression" on "input column "Column 13" (11693)".
Let me know which is the next step or what guidelines must I follow on this issue.
|||In your expression you refer to "Column13" but the SSIS error message shows that the column in the data flow is in fact named "Column 13"
Donald
|||A couple other things:
The expression language does not have an IsDate() function. What exactly are you testing for with that? Itstill might be possible, depending what you are looking for...
There is also Left() function -- you should use Substring() for that.
Instead of "Nothing", you need to use the NULL() functions, which are strictly typed.
|||Mark Durley wrote:
There is also Left() function -- you should use Substring() for that.
Another alternative to the lack of a LEFT() function is RIGHT(REVERSE())
-Jamie
|||Thanks a lot to all of you|||Well, this follow failing:
Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing
Column 13 on the first row own this value:
20050214
TITLE: Microsoft Visual Studio
Error at CargaModelo187 [Derived Column [10345]]: Attempt to find the input column named "Nothing" failed with error code 0xC0010009. The input column specified was not found in the input column collection.
Error at CargaModelo187 [Derived Column [10345]]: Attempt to parse the expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
Error at CargaModelo187 [Derived Column [10345]]: Cannot parse the expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing". The expression was not valid, or there is an out-of-memory error.
Error at CargaModelo187 [Derived Column [10345]]: The expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing" on "input column "Column 13" (12091)" is not valid.
Error at CargaModelo187 [Derived Column [10345]]: Failed to set property "Expression" on "input column "Column 13" (12091)".
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
BUTTONS:
OK
|||I was wrong although this new line-version fails too...
[Derived Column 1] == Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4)
Thanks in advance,
|||It's already solved.
Instead of & you must use + and drop '[Derived Column 1] =='
RIGHT([Column 13],2) + "/" + SUBSTRING([Column 13],5,2) + "/" + SUBSTRING([Column 13],1,4)
IsDate function is not necessary because of I saw concretly in that file that all of them have the same structure yyyymmdd
|||In any case what function is intented do the same that ISDATE?|||There is no function that can do IsDate. As Mark suggested there may be possibilities using other functions - checking that a string has certain characters and that substrings, can be cast to valid integers - but that expression would likely be rather complex.
Script component does all this really nicely, of course.
Donald
