Assigning multiple values in a parameter of Execute Sql task --SSIS

Question

I am creating an SSIS package which uses Execute sql task and Script task. this package displays table contents based on the parameter.

I gave below query in the execute-sql task

 Select * from Production.Product Where ProductID in ( 316,324)

I need to assign the values 316, 324 into a parameter and populate result-set. how to assign multiple values in a parameter variable? please help

Ferdipux · Accepted Answer · 2016-09-20 11:50:48Z

1

If you have less than 2000 values in your CSV list - you can create a String variable Params with value 316, 324 and another String Variable SQL_Select with the following expression:

"Select * from Production.Product Where ProductID in ("+@[User::Params]+")"

In your Execute Task select variable as a query source and define User::SQL_Select as the source. Expression will be evaluated as you access it, and will yield the select statement.

answered Sep 20, 2016 at 11:50

Ferdipux

5,2791 gold badge22 silver badges34 bronze badges

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

2 Comments

bmsqldev Over a year ago

thanks. but i wonder what happens if csv list is more than 2000? is it a limitation of ssis?

Ferdipux Over a year ago

@bmsqldev, it is a limitation of MS SQL. It can handle no more than 2100 params, which means that in a SELECT query you can have no more than 2099 constants or values, 1 param is a query text itself. I roughly downsized it to 2000 in the answer.

neer · Accepted Answer · 2016-09-20 10:10:16Z

1

DECLARE @String nvarchar(Max),@ProductID varchar(25)

SET @productID ='316,324'

SET @String=''
SET @String='Select * from Production.Product Where ProductID in ( '+@productID+')  '
Print @String  
EXECUTE (@String)

edited Sep 20, 2016 at 10:10

neer

4,0926 gold badges22 silver badges34 bronze badges

answered Sep 20, 2016 at 10:09

Alfaiz Ahmed

1,7261 gold badge13 silver badges17 bronze badges

Collectives™ on Stack Overflow

Assigning multiple values in a parameter of Execute Sql task --SSIS

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related