How to pass multiple values in where statement in SSIS variable?

Question

I'm creating a SSIS package which has two variables that is going to assign values into another SQL Command variable. The data source is an ODBC connector and the destination is an OLEDB connector.

Variables name:

vPurchaseType (multiple values)
vTransactionDate (single value)
vPSourceQuery (SQL statement that is receive the others two variables)

One variable is a single row, this one it's working. The other variable is multiple values that is going to be ingested in a IN clause. But I'm not being able to pass multiple values in this one, I already tried Single row or Full result set but also doesn't work.

First variable result (vPurchaseType):

Query example:

SELECT Head FROM TableB

Head
12
9C
AA

The second variable must be between single quotes in order to work.

Second variable result (vTransactionDate):

Query example:

SELECT Head FROM TableA

Head
2020-01-01

Third variable:

SELECT
  ColA,
  ColB,
  ColC,
  ColD
FROM TableC
WHERE ColC >= '"+ @[User::vTransactionDate] + "'
AND ColD IN ("+ @[User::vPurchaseType] +")

My package:

You can't - at least not the way you're doing things. What is the source RDBMS that populates vPurchaseType? — billinkc
– billinkc, Commented Dec 27, 2021 at 18:56
That's bad. Can you please show me the way? I'm quite new at SSIS. The source is Teradata. — Guilherme Matheus
– Guilherme Matheus, Commented Dec 27, 2021 at 18:58
Sorry, what do you mean @Larnu? The only parameters that I'm using is about permissions (user and password), the SQL scripts is variables. — Guilherme Matheus
– Guilherme Matheus, Commented Dec 27, 2021 at 19:31
ColC >= '"+ @[User::vTransactionDate] + "' that is injection. You are injecting the variable into the string. A major security vulnerability. Always, always, always parametrise. — Thom A
– Thom A ♦, Commented Dec 27, 2021 at 19:36

Guilherme Matheus · Accepted Answer · 2021-12-29 17:02:48Z

2

Basically I'm using the solution of this article from MyTecBits - SQL Server: Concatenate Multiple Rows Into Single String, and applying that to my variable vPurchaseType, and receiving the values as Single Row. But I had to change the query a little bit, by using the function CAST AS VARCHAR (4000) to accept all the data from my column.

After that I changed my Data Flow, and did the swap from OLEDB Destination to ADO Net Destination, I also had to mark the option ValidadeExternalMetadata as False, and that's it! Now is working.

answered Dec 29, 2021 at 17:02

Guilherme Matheus

64918 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

How to pass multiple values in where statement in SSIS variable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related