Remove Null value from dt_ntext field

• RustyT

  Right there with Babe

  Points: 758

  More actions

  May 16, 2008 at 9:22 am

  #125355

  In my ssis package I am taking data from an access db and inserting it into sql server 2005. Here is an example of the tables.

  Access Table

  Field1 memo

  Sql Server table

  Field1 nvarchar(max) not null

  My issue is that when I have a null value in the memo field and append it to sql it gives me an error that i am violating integrity constraints. I understand the reason for the error, but I am not sure how to check and replace a null in the field. I tried to use (ISNULL(Field1) ? "" : Field1) but it says:

  The data types "DT_WSTR" and "DT_NTEXT" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

  Are there any suggestions on how I can replace the null value or will I have to change my field to allow nulls?

  Thanks for any help you can give

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  May 19, 2008 at 10:54 am

  #817313

  hi,

  You have to use script task to check for null and replace it with ""

  Try this:

  If Row.Column1_IsNull = True Then

  Trim(Row.Column1) = ""

  Else

  Row.Column1 = Trim(Row.Column1)

  End If

  -V

• RustyT

  Right there with Babe

  Points: 758

  More actions

  May 19, 2008 at 10:58 am

  #817317

  Thanks, I will give that a shot.

• maximost1

  Mr or Mrs. 500

  Points: 515

  More actions

  January 20, 2009 at 4:27 pm

  #930015

  Where would the script task be placed? After the flat file and before the next transformation? I am having the same issue.

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  January 20, 2009 at 4:39 pm

  #930017

  Hi,

  I believe your source is flat file, then sequence goes like this:

  faltfile source

  data conversion task

  Script task

  Destination.

  Is that what you wanted to know?

  -VG

• lokesh_kumar03

  SSC Rookie

  Points: 45

  More actions

  June 13, 2016 at 5:26 am

  #1884607

  I got the same error.

  Here is the conversion :

  (ISNULL(Mobile)?"": (DT_WSTR,1024)Mobile) != (ISNULL([Dest_Mobile] )?"": (DT_WSTR,1024) [Dest_Mobile] )

  Below article is nice for reference

  http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx