SSIS. Use results of SQL Server as a where clause in a Vertica Query

I have a view in SQL Server that detects all the Products Ids from a table that does not have a Product Description. In vertica we have all the Product IDs with their description. My idea is to build a SSIS package that can pull all the Products Ids with missing description and use it as a where clause in the Vertica query.

I already tried with user variables but I failed. I am beginner on SSIS

This is what I am looking for.

SELECT [Product ID], [Product Desc]
FROM VerticaTable
WHERE [Product ID] IN (@Variable?)

The variable should like like this.

SELECT DISTINCT [Product ID] FROM SQLSerrverViewThatHasMissingDesc

Solution

Basically you have two ways to do that.

First of all, I would like to let you know the SELECT in the SSIS package will not show you anything, but you can make the changes to the query, such as insert the results into a table.

Create a Object type variable1 and use Foreach Loop Container to iterate that variable1, assign each of the value to another variable2 (created in advance, maybe it is a String type variable). Then in the container, put you Execute SQL task with the query:
```
SELECT [Product ID], [Product Desc]
FROM VerticaTable
WHERE [Product ID] = ?
```
Map variable1 as Index 0 in the parameter mapping page. (if you are using OLE DB as Connection type)
- The drawback using this method is you cannot view all the productID at the same time because this is a loop process,unless you would like to load each of the result to a table.
If you prefer to see all the candidate results at the same time, the most common way is using Dynamic SQL because variables are not supported anywhere in your query.

So, the same query as you wrote, but need some add-ups.

1) First, Create three variables, variable1, variable2, variable3, variable1 is a Object type and variable2 and variable3 are String type.

2) Next, you need a Foreach Loop Container to build up your variable. In the Foreach Loop Container, drag and drop Script Task. Before the container, connected by a Execute SQL task, and after the container, connecting another Execute SQL task

So physical Order: Execute SQL task 1 -> Foreach Loop Container, inside: Script task -> Execute SQL task 2

3) Open the Execute SQL Task 1, put SELECT DISTINCT [Product ID] FROM SQLSerrverViewThatHasMissingDesc in the query, and go to the Result set page, assign Variable1

4) Move on to the Foreach loop Container, choose Foreach ADO Enumerator, then choose Variable1 as ADO object source variable, mode is Rows in the first table, then go to Variable mappings page, choose Variable2, and default the index.

5) Go to the Script task, open it, choose Variable as ReadOnlyVariable, in the ReadWrite variable, choose Variable3, edit the script, paste the follow code inside the Main()

    if (Dts.Variables["User::Variable3"].Value.ToString() == "")
    {
        Dts.Variables["User::Variable3"].Value = Dts.Variables["User::Variable2"].Value.ToString();
    }
    else
    {
        Dts.Variables["User::Variable3"].Value = Dts.Variables["User::Variable2"].Value.ToString() + "," + Dts.Variables["User::Variable2"].Value.ToString();
    }
    Dts.TaskResult = (int)ScriptResults.Success;

6) After above step, ideally, your Variable3 will have a long string separated by ,, like A,B,C,D,E. The reason you want to get this string is because this Variable3 will be used later in the Execute SQL task as part of the Dynamic SQL

7) Here comes the final step, in the Execute SQL Task 2, use following query:

DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT [Product ID], [Product Desc]
FROM VerticaTable
WHERE [Product ID] IN (''' + ? + ''')'

In the parameter mapping, choose Vareiable3, index as 0.

8) Execute the package.

But it will not show you anything, but if you try to simulate the dynamic process in SSMS, you will see the result.