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:

Execute

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:

Results

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 !!

One thought on “SSIS: An error occurred while assigning a value to the variable

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s