SSIS: An error occurred while assigning a value to the variable

Recently, while working on SSIS, we came across a scenario where we had to assign multiple values to multiple variables to use them in package later.

We used “Execute SQL Task” to assign those values to the variables. The SQL query we used, was returning single row with 12 columns. We wanted to store the values of 12 columns in 12 variables.

Below is the screenshot of the Execute SQL Task Editor:

Then, we mapped the columns returned from the select statement query to 12 variables using Result Set section on the left panel as shown below:

Then, while executing the task to see if it’s working or not, we got the error: “An error occurred while assigning a value to the variable“.

Reason: We were using varchar(max) as the data type of the column that we were selecting using select statement.

Fix: We changed the data type of those columns that we were selecting to varchar(7000) and it worked. All the column values got assigned to the respective SSIS variables.

Basically, SSIS variables are unable to hold varchar(max) or nvarchar(max) values. Hope it helps !!