May 13, 2011 at 10:40 am
I'm adding a column thru a Derived Column transformation to pass to a existing SQL table, but it's showing in red, meaning there's a syntax error in the expression, however, the syntax works just fine in t-sql, but can't figure out what I need to change in the ssis syntax.
t-sql syntax: UPPER(LTRIM(RTRIM(SUBSTRING(rcpt,CHARINDEX('@',rcpt)+1,50))))
ssis syntax: UPPER(LTRIM(RTRIM(SUBSTRING(rcpt,FINDSTRING(rcpt,'@',1)+1,50))))
What I need the code to do: parse the email (rcpt) field and pull the domain along with .com/org, etc, for example, joesmith@hotmail.com would give me hotmail.com. I've already parsed the field successfully in sql for the existing records, but now I need my ssis process to do it for any future incoming records.
Any clue?
Thx,
John
May 13, 2011 at 11:34 am
Hi-lite the row with the error in the expression and click the "Configure Error Output" button at the bottom of the dialog. What error information does it return?
May 13, 2011 at 12:10 pm
I see. cool!! It pointed to the single quotes around the '@'. In t-sql, we use single ' ticks, but I guess in ssis we gotta use double " ticks around. Now the expression is no longer red. So that seems to work. My next question is though, with my new syntax below, will it perform exactly the same operation as my t-sql syntax below?
ssis epxression: UPPER(LTRIM(RTRIM(SUBSTRING(rcpt,FINDSTRING(rcpt,"@",1)+1,50))))
t-sql syntax: UPPER(LTRIM(RTRIM(SUBSTRING(rcpt,CHARINDEX('@',rcpt)+1,50))))
Thx,
John
May 13, 2011 at 1:00 pm
Those single quote double quote issues get me ALL the time.
My brain is just about shut down after a long hard week, as a matter of style I would probably write a different expression (no particular reason, just my personal style and the commands I prefer) but it looks like it ought to work .
May 13, 2011 at 1:02 pm
Both expressions seem the same to me.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2011 at 2:08 pm
Great!. thx.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply