May 16, 2008 at 9:22 am
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
May 19, 2008 at 10:54 am
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
May 19, 2008 at 10:58 am
Thanks, I will give that a shot.
January 20, 2009 at 4:27 pm
Where would the script task be placed? After the flat file and before the next transformation? I am having the same issue.
January 20, 2009 at 4:39 pm
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
June 13, 2016 at 5:26 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply