How to pass a parameter in a SQL query in MS SQL Server from an Excel sheet

Question

Let's say I have this query in SQL Server:

select * 
from Table1 
where Column1 IN ('01061120192T') 

I want to pass the values for Column1 from a column in an Excel sheet.

I'm able to pass a single value to the query as mentioned here. But parenthesis of IN can accept multiple values so I want to pass 1000 values in the parenthesis from a column in an Excel sheet.

Tried to pass multiple values to the parameter as below, but that's not working.

=Sheet1!$G$1:$G$5

Answer 1

You pass variables in SSIS as a ?, and then define your variables in your parameter mapping pane.

select * 
from Table1 
where Column1 = ?

Then, you need to go to your Parameter mapping pane and click Add. Select your variable's name in the Variable name Column. Then Input as the direction. Leave Paramter Size as -1.