How to pass in clause values via SSIS paramter

Question

Hi,
I have created SSIS package based on the query( big query),In query in clause is there.
I have created parameter in ssis level and trying to pass multiple values(a,b) to in clause ,It is not working how i can achieve.

Can you please suggest how i can do (Without assigning entire query to variable)

WHERE column not in(?)

Answer 1

Hi @KlearnSSIS ,

1.Could you please share the example data and sql query?
May I know where do you execute the sql query? In Execute SQL Task or Data Flow Task?

2.Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers. For example, the ADO.NET connection manager type requires that the SQL command uses a parameter marker in the format @varParameter, whereas OLE DB connection type requires the question mark (?) parameter marker.
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
Please refer to Parameters in the Execute SQL Task.

3.The following SQL statement selects rows from the Product table in the AdventureWorks2012 database. The first parameter in the Mappings list maps to the first parameter to the Color column, the second parameter to the Size column.
SELECT Color, Size FROM Production.Product WHERE Color = ? AND Size = ?
Please refer to Using Parameterized SQL Statements.

Best regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

WHERE column not in(?)

IN clause with Parameter don't work.
Use a table, fill in with the parameters in mind and use it to filter, like

WHERE NOT column IN (SELECT FilterColumn FROM FilterTable)

Answer 3

I realize this is rather old but I'll put my solution here in case someone else stumbles on it.
I just had to do this and I used a varchar variable to catch the param. Looking for field3 of long values but using a string param

param searchItems; type string; value 12345,54321; sensitive false; required true;

DECLARE @tempItems varchar(4000) = ?
SELECT field1, field2 from sourceTable where field3 IN (
SELECT value FROM string_split(@tempItems, ',')
)

So, the @tempItems catches the incoming parameter and string_split provides the source for select value

HTH, Mike