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

you can declare a variable, pass it a parameter and use it in the Where clause. take a look to my answer — Hadi
– Hadi, Commented Apr 5, 2017 at 18:10

Hadi · Accepted Answer · 2017-04-05 18:09:29Z

1

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] = ?

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

Set the expression to the following:

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

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

edited Apr 5, 2017 at 18:09

answered Apr 4, 2017 at 20:10

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

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

1 Comment

user3428422 Over a year ago

I see, that was pretty simple to be fair. I am ashamed of my question. thanks

Rich · Accepted Answer · 2017-04-04 13:24:26Z

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?

answered Apr 4, 2017 at 13:24

Rich

2,2801 gold badge23 silver badges28 bronze badges

Comments

benjamin moskovits · Accepted Answer · 2017-04-04 13:28:05Z

1

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

answered Apr 4, 2017 at 13:28

benjamin moskovits

5,5441 gold badge14 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related