SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use REPLACE in a Derived Column Transformation?


How to use REPLACE in a Derived Column Transformation?

Author
Message
lindsayscott23
lindsayscott23
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 406
Hi,

Long time lurker, first time poster. Hope you can help!

I'm using SSIS to import a flat file into a db and want to clean up data in a column (called 'preci') by removing some tilda (~) characters.

Iv'e created a derived column transformation and added it to that package date flow task (after the flat file source and before the OLE DB Destination)

i've used the expression to try to perform the REPLACE:

select replace([precis],'~','')

as part of the following:

Derived Column Name
PRECIS
Derived Column
Replace 'PRECIS'
Expression
select replace([precis],'~','')
Data Type
string [DT_STR]
Length
2000
Code Page
1252 (ANSI - Latin I)

But keep receiving the following error:

TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [DC_Precis [255]]: Attempt to parse the expression "select replace([precis],'~','')" 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 [DC_Precis [255]]: Cannot parse the expression "select replace([precis],'~','')". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [DC_Precis [255]]: The expression "select replace([precis],'~','')" on "input column "PRECIS" (268)" is not valid.

Error at Data Flow Task [DC_Precis [255]]: Failed to set property "Expression" on "input column "PRECIS" (268)".
------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

I use SSIS quite a bit, but am new to trying to clean any data on import.

Any help or insight into what I'm doing wrong would be really cool.

Cheers

Lins
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19412 Visits: 20462
Try changing your expression - you do not need the 'select' bit. See here for reference.

Also, note that literal text should be surrounded by " rather than ' in SSIS expressions.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
lindsayscott23
lindsayscott23
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 406
Hi Phil, Thanks for the speedy response

i have rewritten the expression as follows:

replace([precis],"~","")

but am still seeing the error msg pasted below.

There seems to be a problem with the input column not being in the input column collection.

But Precis does appear in the column options in the derived Column transformation Editor.

Is it possible that I'm using the derived colum trnasform in tha wrong place in my package DFT?



TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [Derived Column [628]]: Attempt to find the input column named "precis" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at Data Flow Task [Derived Column [628]]: Attempt to parse the expression "replace([precis],"~"," ")" 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 Data Flow Task [Derived Column [628]]: Cannot parse the expression "replace([precis],"~"," ")". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [628]]: The expression "replace([precis],"~"," ")" on "input column "PRECIS" (719)" is not valid.

Error at Data Flow Task [Derived Column [628]]: Failed to set property "Expression" on "input column "PRECIS" (719)".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19412 Visits: 20462
Hmm - that should work.

Can you attach a screen shot which shows the data flow along with the derived column expression?

As long as the derived column is somewhere between your source and destination, placement shouldn't be a problem.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19412 Visits: 20462
(clutching at straws...)

One thing worth checking: try changing the tilde (~) to something else (eg 'x'), just in case it is being interpreted as a special character. It does have a special meaning in C# (which is what the SSIS expression language appears loosely based on).


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
lindsayscott23
lindsayscott23
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 406
Hi,

It works! I was just rebuilding it to put together the screenshot for you.

I used the expression as below (using [] around the table name which SSIS has now dropped)

REPLACE(PRECIS,"~","")

and it now accepts it.

Perfect. Thanks loads for your help and time.

Cheers

Lins
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19412 Visits: 20462
No problem.

Within the SSIS data pipeline, there is not really a concept of table names. The source data values are copied to in-memory variables which then flow through the pipeline (in the data flow), to be sent to whatever destination you have specified.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search