question

KlearnSSIS-1526 avatar image
0 Votes"
KlearnSSIS-1526 asked Monalv-msft commented

How to pass in clause values via SSIS paramter

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(?)



sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @KlearnSSIS-1526 ,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KlearnSSIS-1526 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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)



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.