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

Answer 1

Few ways to solve your problem:

1. Easiest way is change the data type from string to object
2. From the first Execute SQL task, put ISNULL around the issue column, which will make sure the output is string
3. 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