SSIS + Sql server 2005

Question

Post reply

SSIS + Sql server 2005

swatianand83

SSC Eights!

Points: 930
More actions
May 13, 2008 at 1:45 am

#121994

I am using Sql server 2005 as a database.
While using SSIS Derived Column Transformation Editr control, i have added 2 new columns to resultant datasets and their values are being set as:
Date = (case when charindex('TDt', Description,1) != 0 then Assessment_Date else 0 end)
but the above command gives an error that : 'Attempt to parse the expression failed. Might contain an invalid or incomplete token..... '
Please suggest what could be an issue here.
thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gordon Radley SSCommitted Points: 1776 More actions · Answer 1

You need to use SSIS scripting inside derived column tasks. Try the following:

FINDSTRING( [Description] ,"TDt",1) != 0 ? [Assessment_Date] : "0"

Set the Derived Column Name to

Date

swatianand83 SSC Eights! Points: 930 More actions · Answer 2

It still gives the error as FINDSTRING is not an in-built function.

Can you please help.

Thanks

Gordon Radley SSCommitted Points: 1776 More actions · Answer 3

Can you please be more specific on the error you are receiving?

FINDSTRING is one of the valid SSIS string functions. If you expand the "String Functions" folder in the top right, you will see it there.

swatianand83 SSC Eights! Points: 930 More actions · Answer 4

I am getting data from an excel file. There is a column called 'Description'. That column is used to separate the entities for 2 types of dates: Transaction and Flow.

While i try and add 2 new columns corresponding to these dates thru 'Derived Column Transformation Editor' by giving the expression as the case statement mentioned in the previous post, it gives an error. Same error comes when i give the FINDSTRING function.

Can you sugest some way of doing it.

Thanks

Gordon Radley SSCommitted Points: 1776 More actions · Answer 5

You cannot output to 2 columns using a single expression in Derived Column Transformations. Here is what I used locally and got it to work fine. It adds a new field called New_Date and populates it with Assessment_Date if string "TDt" is found in field Description, otherwise it outputs 0 to the New_Date field:

Derived Column Name Derived Column Expression

New_Date FINDSTRING( [Description] ,"TDt",1) != 0 ? [Assessment_Date] : "0"

Is Assessment_Date also a column in the Excel spreadsheet?

swatianand83 SSC Eights! Points: 930 More actions · Answer 6

Ya, assessment_date is also a columns extracted from the excel sheet.

Gordon Radley SSCommitted Points: 1776 More actions · Answer 7

I don't understand why this is not rendering as a valid expression. I assume it appears in red when you paste it into the expression column? When you mouse over the expression displaying the popup error, can you see any other details other than "not a built-in function". I definately know the case statement will not work here.

Hopefullly someone else in this forum can assist you with this issue.

Here is a link to the Books on Line section for the FINDSTRING SSIS function. Maybe it will shed some light on the syntax being used:

http://msdn.microsoft.com/en-us/library/ms141748.aspx

swatianand83 SSC Eights! Points: 930 More actions · Answer 8

Hey, thanks.

the FINDSTRING this worked. but the conditional operator condition is still raising an error.

The error is: 'Incompaible types for operator " != ". It needs to be explicitly casted.

Can you just help.

thanks a ton.

swatianand83 SSC Eights! Points: 930 More actions · Answer 9

hey. the problem got resolved.

FINDSTRING function worked. requried some casting to be done to make up for the incompatibility between the data types.

thanks a ton for ur help.

Gordon Radley SSCommitted Points: 1776 More actions · Answer 10

Glad to hear you got the issue resolved.

Yeah casting values correctly has caused me problems in the past as well.