ssis derived transformation

hi all,
I have a sql query where there is NULL in some of the columns. I'm trying to create a delimited text file as destination.
Instead of NULL in Text file while data is passing thru dervied transformation , I want NULL to be replaced as Nothing.

In the text file the columns are having a delimiter as Pipe. Data for NULL should be replaced by nothing and the text columns in the file should look empty inbetween  pipe  like ||   . As u  can see inbetween there in no null . its nothing with no quotes.
Who is Participating?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'nothing' in simple English, as a NULL will be passed to a text file as an empty string ''.
If for whatever reason that's not happening for you, then in each column where there may be a null, use this in T-SQL:

Instead of:    SELECT one, two, three
Use this:        SELECT ISNULL(one, '') as one, ISNULL(two, '') as two, ISNULL(three, '') as three
Do you see the word "NULL" in your file like this:


but you want:


That is an "empty string" which is given by 2 single quotes, no other character in between. This is different to NULL.

If this is what you are seeking then ISNULL() as shown by Jim should do the job.

You can use COALESCE() as an alternative to ISNULL()

I would suggest you avoid describing this as "nothing" :)
Truly difficult to give you something for nothing.
learner0824Author Commented:
I dont want to do it at sql level but in SSIS . If so, should I use a dervied transformation and what is the expression to use
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.