The type of the value being assigned to variable differs from the current variable type

Question

My SSIS package fails with the error:

The type of the value being assigned to variable differs from the current variable type.

I declared a variable of type string and corresponding column name in SQL table is varchar(33). If I use data type as object it succeeds but again i need to use the variable value in an expression for connection string and there object type is not supported.

Please help me how to proceed.

Note: The goal of my SSIS package is to get server list from a table (that's where it fails) and execute some script with a foreach loop container in all the servers.

I have two links http://stackoverflow.com/questions/21009091/store-sql-server-varchar-max-in-ssis-string and http://stackoverflow.com/questions/14705639/how-to-transfer-data-using-ssis-when-database-columns-are-defined-as-varcharmax — Santosh Dhanawade, Aug 14 '15 at 12:09

score 2 · Accepted Answer · answered Aug 14 '15 at 13:04

If I'm reading your question right, what you actually need are two variables: one is an Object variable that receives the data from your SQL table, and one is a String value that you will pass to your ForEach loop.

The difference between them is that your Object variable can hold multiple rows, while your String value can only hold a single item at a time. What you will do is declare an Object variable, populate it with a list of the servers, then use a ForEach loop to step through each item in the list, feeding the values one at a time to your String variable. You will then use the String variable to set up the script you mentioned, and execute it once per server.

For further reading, there is an excellent walkthrough here that will give you screenshots and examples of what I am talking about.

Thank you! Exactly what i needed! – RMu Aug 14 '15 at 15:24 — RMu, Aug 14 '15 at 15:24
Excellent !! years later, you've saved the day ! – Joe Mar 27 '18 at 17:40 — Joe, Mar 27 '18 at 17:40

score 1 · Answer 2 · answered Aug 24 '16 at 04:27

I know i am replying to this thread very late, below explanation is for people who see this in future. I encountered this situation recently. I did the below

Firstly my Record Set Destination Variable of object type. Using For Each Loop container, i mapped each and every row data(with multiple columns) to an variable(s) of OBJECT type. When i use any other datatypes other than OBJECT datatype for variable(s) i got the error as below

Error: ForEach Variable Mapping number 3 to variable "User::EMPID" cannot be applied.

Then I used the variable(s) of object type in my Execute SQL Task and it inserts data successfully without any error.

Thumb Rule: Map Record Set Destination output to the variable of OBJECT type.

score 0 · Answer 3 · answered Aug 23 '16 at 12:54

I encountered this situation recently. I did the below

Firstly my Record Set Destination Variable of object type. Using For Each Loop container, i mapped each and every row data(with multiple columns) to an variable(s) of OBJECT type. When i use any other datatypes other than OBJECT datatype for variable(s) i got the error as below

Error: ForEach Variable Mapping number 3 to variable "User::EMPID" cannot be applied.

Then I used the variable(s) of object type in my Execute SQL Task and it inserts data successfully without any error.

Thumb Rule: Map Record Set Destination output to the variable of OBJECT type.

The type of the value being assigned to variable differs from the current variable type

3 Answers3