H

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!