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

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

Second variable result (vTransactionDate):

Query example:

SELECT Head FROM TableA

Third variable:

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

My package:

CodePudding user response：

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.