SSIS - specify only one parameter in OLE DB Source with multiple '?'

Question

when writing my SQL command I specify ? to indicate that I want to use a passed in Parameter. However, for every ? I use, the package thinks it's a new parameter even though I want to use the same one.

6 ? = 6 new parameters though each parameter is the same. As seen on parameter 0 and 1

Is there a way that I can specify just one parameter?

Thanks

Answer 1

Could you declare a variable inside your SQL statement script that takes the value of the parameter via the ?, and then use the variable after that?

Answer 2

Create a stored procedure on SQL Server that takes one parameter and contains the correct query. Invoke the stored procedure with the one parameter.

Answer 3

You can achieve this by declaring a variable in you SQL Command and pass it a parameter then use this variable in the WHERE clause. like the following

DECLARE @str AS INT

SET @str = ?

SELECT        Column1
FROM            Table1
WHERE        (Column2= @str or Column3 = @str)

Other Workarounds

1)

Assuming you want to create the following query

Select * From Table1 Where [Column1] = ? Or [Column2] = ?

1. Declare a variable (ex: User::strQuery)
2. Store your parameter in a variable (ex: User::Value)
3. Set the variable EvaluateAsExpression property to True

4. Set the expression to the following:

   "Select * From Table1 Where [Column1] = " + @[User::Value] + " OR [Column2] = " + @[User::Value]
   

5. In the OLEDB Source Set the source to SQL Command from variable and choose the variable @[User::strQuery]

2)

You can create a stored procedure or a table-valued function that take one parameter and use it as source