Assign String variable in SSIS

Question

I have a execute sql task in SSIS that I try to store a string value

this is the query

Declare @IdList nvarchar(max)
select @IdList = coalesce( @IdList + ''',''', '') + ID from table
Select @IdList = '''' + @IdList 
Select @IdList

The result looks like

'abc','bcd','def','fds'

and I try to store this value into String variable in SSIS

result set: single row
result name: 0 
Variable Name: String_Contact

this is the error I got

[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "String_Contact": "The type of the value (DBNull) being assigned to variable "User::String_Contact" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.

".

Anyone know what's the issue?

thanks

Can you try adding a Set @IdList='' after the declaration and see if that works? Also your type is nvarchar(max) which SSIS would intrepret as DT_NTEXT. You can try with an nvarchar(1000). — TMNT2014
– TMNT2014, Commented Jun 24, 2014 at 15:47
Will try that, thanks! but for the data type, the length of the string might exceed 100000 so I have to use nvarchar(max) — user3399111
– user3399111, Commented Jun 24, 2014 at 15:50

Vikramsinh Shinde · Accepted Answer · 2014-06-24 16:20:17Z

8

Try to change the data type of the variable from String to Object.

If you assign varchar(max) to a string [SSIS] variable. there might be chance of overflow error since strings in SSIS are limited to 8000 chars in length.

But you can assign nvarchar(max) to Object.

You can process object by using For Each Loop Container setting Enumerator as "For Each ADO Enumerator" or by Script Task as

 DataTable dt = new DataTable();
OleDbDataAdapter oleDa = new OleDbDataAdapter();
oleDa.Fill(dt, Dts.Variables["User::variable"].Value);

use dt as regular DataTable.

Resources:

How To Read Object Type Variable In Script Task

Using the SSIS Object Variable as a Result Set Enumerator

Execute SQL Task into an object variable - Shred it with a Foreach loop

Mapping Full Resultset with Execute SQL Task to Object Variable

To use variable in SQLStatement property follow this thread.

edited Jun 24, 2014 at 16:20

answered Jun 24, 2014 at 15:58

Vikramsinh Shinde

2,8832 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3399111 Over a year ago

it will work, but how can I reference this object variable in my data flow source in this case? .. My goal is to use the variable in the source query such as: Select * from table where ID in (@variable)

TMNT2014 Over a year ago

Why don't you use a lookup task if you are just just looking up ids?

user3399111 Over a year ago

that's what I am using now, but I am trying different thing to see if performance increased. cause I am dealing with million of rows here. thanks for the comment again

Kyle Hale · Accepted Answer · 2014-06-24 16:29:19Z

1

Vikamsinh's answer is technically correct, but he gives you the wrong solution.

To put it simply: you cannot do what you wish to do if the string variable you wish to insert into your SQL command exceeds 8000 characters. Since you'll have to use an NVARCHAR(MAX), SSIS will only interpret that as an Object variable, and you can't convert it to a String.

According to your comments, you might have a string of 100,000 characters. (So many IDs!) S

There is a pretty simple solution, though: use a Script Source Component instead of a standard source component. Then you can cast your object variable's value to a .NET String Type, build up your SQL statement there, and execute it against your connection to read in your results.

Here's the MSDN sample on using a Script Source Component.

That all being said, is your original source a large set of data? Could you change this to a "select fields from table" source and use a Lookup to filter using the IDs you're drawing out of your Execute SQL task? Or (even better) can you push your IDs into a staging table on your source database and then use a join to limit your source query to the rows you want to intake?

answered Jun 24, 2014 at 16:29

Kyle Hale

8,1301 gold badge40 silver badges59 bronze badges

1 Comment

user3399111 Over a year ago

Thanks, now I know the reason why it's failing and the option to fix it.

Collectives™ on Stack Overflow

Assign String variable in SSIS

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related