ISNULL function in SSIS

sql-server unicode ssis isnull non-unicode
Ajinkya Pujari picture Ajinkya Pujari · Jun 1, 2018 · Viewed 7k times · Source

I have a column PhoneNumber varchar(50) and i want to build an expression for a derived column PhoneNumberType varchar(50) based on below,

IF PhoneNumber <> NULL then
Set to ""Office""
Else
Set to NULL
END IF

I tried as 

!ISNULL(PhoneNumber ) ? "Office" : PhoneNumber

But i am getting an error while mapping the column as

Column PhoneNumberType can not convert between unicode and non-unicode string data types

Update : Can i just go to the advance editor of derived column component and select string [DT_STR] instead of auto generated , unicode string [DT_WSTR] data type in column properties ?

is this a good practice ?

Answer

Hadi picture Hadi · Jun 3, 2018

You can use the following expression to generate a DT_STR column and it also check for empty strings:

ISNULL([PhoneNumber]) || TRIM(PhoneNumber) == "" ? NULL(DT_STR, 50, 1252) : (DT_STR,50,1252)"Office"

Related questions

When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?

Is there a rule when we must use the Unicode types? I have seen that most of the European languages (German, Italian, English, ...) are fine in the same database in VARCHAR columns. I am looking for something like: If you …

Read More
Is it possible to have SQL Server convert collation to UTF-8 / UTF-16

In a project I am working on my data is stored in SQL Server, with the collation Danish_Norwegian_CI_AS. The data is output'ed through FreeTDS and ODBC, to python that handles the data as UTF-8. Some of the …

Read More
Inserting special characters (greater/less than or equal symbol) into SQL Server database

I am trying to insert ≤ and ≥ into a symbol table where the column is of type nvarchar. Is this possible or are these symbols not allowed in SQL Server?

Read More