Can I set variables in an SSIS loop for a query based loop?

Question

I have an SQL query that runs in SSIS to load data into a CSV file that looks something like this:

SELECT * FROM SomeTable WHERE SomeDate BETWEEN '1-Jan-2016' AND '31-Dec-2016' AND Param1 = 2 AND Param2 = 2 

When it was written in QlikView, I used the following parameters:

 SELECT * FROM SomeTable WHERE SomeDate BETWEEN '1-Jan-2016' AND '31-Dec-2016' AND Param1 = $(Param1) AND Param2 = $(Param2) 

Now that I am transferring the entire task to SSIS, I will figure out how to get it so that Param1 and Param2 are dynamically assigned. For example, in QlikView, I created a table populated with another query:

 SELECT Param1, Param2 FROM ThisTable WHERE SomeID = 1 

Something like that. Choosing Param1 and Param2 from this query gets me the necessary values ​​for $(Param1) and $(Param2) in my QlikView code.

I'm trying to convert QlikView code to SSIS package now because SSIS is a special ETL tool, while QlikView is not. Is what I am making possible? And if so, how do I do this?

My idea was to wrap it all in a for loop container and stop it after it Param1 last Param1 and Param2 from this request:

 SELECT Param1, Param2 FROM ThisTable WHERE SomeID = 1 

Basically, I'm trying to avoid having to write my first select statement a thousand times.

Thanks.

If what I am saying does not make sense, please let me know so that I can develop a little more.

Answer 1

I suspect that you are executing an SQL task, so you can simply display the parameters in the SQL Task component.

What you need to do is first create the SQL component that executes this query:

 SELECT Param1, Param2 FROM ThisTable WHERE SomeID = 1; 

I mocked SQLStatement, but everything else should look like this (don't forget to check that it has a complete dataset):

Then put the result set in an object variable (just make sure the result name is 0):

Now, to run the following query for each value collected above, we can use the foreach loop and iterate over our dataset. In this foreach loop, we will pose a data flow task in which you will use OLE DB as the source and flat file as the destination to read the data and put it in csv files. (In a real project, I would recommend using ODBC instead of OLE DB, this is faster).

Loop Properties:

Assigning variables in a foreach loop:

Now, in your data flow task, create your data source, add a query and configure it like this:

In the end, it should look something like this (what is highlighted in red are internal components of the data flow task):

Of course, you will need to add some entries or some other components, but this is basic and will help you move.

Answer 2

You can also learn the SSIS tools for the Loop Pipeline and Foreach Loop.

Fill the parameter type object with a list of values ​​- in my case, I used the query in the SQL [Lookup missing Orders] task. And then the [Foreight Order Loop] task goes through each entry in the parameter and executes the [Load missing Orders] flow task: