Error: The type of the value (DBNull) being assigned to variable "User:: differs from the current variable type (String)

Question

I am creating a SSIS package and getting the following error while extracting the data.

Error: The type of the value (DBNull) being assigned to variable "User::dunsId" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.

dunsId is varchar(150) in the source table which is nullable column and contains Null value. The variable that I have created in SSIS to map it is of type string. I am basically trying to extract all he records and insert it in the destination table that contains column dunsId is varchar(150)

here is my insert query

INSERT INTO Parties (companyId, dunsId, companyName, companyTypeId,companyTypeName,companyStatusTypeId,companyStatusTypeName,simpleIndustryId,simpleIndustryDescription)  
values (companyId, dunsId, companyName, companyTypeId, companyTypeName,companyStatusTypeId,companyStatusTypeName,simpleIndustryId,simpleIndustryDescription)

Parameter mapping

variables

design

why you need a variable to store the source data? cannot be a directly load? from source to destination? — LONG
What I am trying to do is extract all the data in the first execute sql statement and store it in an object variable. I am then traversing the object in for loop container and trying to execute the insert statement in execute sql task within it. Correct me if I am going wrong anywhere — Tom
hence I have defined the uservariables and done the mappings in the execute sql task to hold the values in it before inserting — Tom
I am not very sure what is the purpose of doing that, for me, you could do in many other ways like what you are trying to do, such as single sql task insert into select * from, or OLE DB source to OLE DB Destination. If you have some reasons with your methods, you could put expression for that variable, which is to use ISNULL to convert NULL — LONG
Where do I set the IsNull, is if where my variables are defined or in parameter mapping etc — Tom

LONG LONG · Accepted Answer · 2017-07-13T12:28:28

Few ways to solve your problem:

Easiest way is change the data type from string to object
From the first Execute SQL task, put ISNULL around the issue column, which will make sure the output is string
Use some alternate ways such as load from Source to Destination in DataFlow task or use one Execute SQL task with INSERT INTO target_table SELECT...FROM Source_table statement

UPDATE

as you mentioned, you are outputting the columns using stored procedure, then you could utilize statement INSERT INTO target_table EXEC your_usp instead of method 3 above

Error: The type of the value (DBNull) being assigned to variable "User:: differs from the current variable type (String)

2 Answers