Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use REPLACE in a Derived Column Transformation? Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 3:53 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:26 AM
Points: 16, Visits: 153
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

Post #1470375
Posted Thursday, July 4, 2013 4:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470386
Posted Thursday, July 4, 2013 4:40 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:26 AM
Points: 16, Visits: 153
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
------------------------------
Post #1470394
Posted Thursday, July 4, 2013 4:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470399
Posted Thursday, July 4, 2013 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
(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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470405
Posted Thursday, July 4, 2013 5:05 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:26 AM
Points: 16, Visits: 153
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
Post #1470406
Posted Thursday, July 4, 2013 5:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470409
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse