July 4, 2013 at 3:53 am
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
July 4, 2013 at 4:14 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 4, 2013 at 4:40 am
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
------------------------------
July 4, 2013 at 4:48 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 4, 2013 at 5:05 am
(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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 4, 2013 at 5:05 am
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
July 4, 2013 at 5:14 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply