Can I set variables in an SSIS for loop based on a

问题:

I have a SQL query that's being executed 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 this was written in QlikView, I used parameters like so:

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

Now that I'm migrating the entire task to SSIS, I'm figuring out how to get it such that Param1 and Param2 would be dynamically assigned. For example, in QlikView, I created a table that was populated by another query:

SELECT Param1, Param2
FROM ThisTable
WHERE SomeID = 1

Something like that. The selection of Param1 and Param2 from that query gets me the necessary values for $(Param1) and $(Param2) in my QlikView code.

I'm right now trying to convert my QlikView code into an SSIS package instead since SSIS is a dedicated ETL tool whereas QlikView isn't. Is what I'm doing possible? And if so, how would I go about doing it?

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

SELECT Param1, Param2
FROM ThisTable
WHERE SomeID = 1

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

Thank you.

If what I'm saying doesn't make sense, please let me know so I can elaborate a bit more.

回答1:

I'm suspecting that you're doing a SQL Task, therefore, you simply can map parameters in SQL Task component.

What you'll have to do is to first create a SQL Component that executes this query:

SELECT Param1, Param2
FROM ThisTable
WHERE SomeID = 1;

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

Then put result set into a object variable (just make sure to Result Name as 0):

Now in order to run following query for each value, gathered above, we can use a foreach loop and iterate over our dataset. In this foreach loop we'll put a data flow task where you'll be using OLE DB as a source and flat file as destination in order to read data and put it into csv files. (In real project I'd advice to use ODBC instead of OLE DB, it's faster).

Loop properties:

Assign variables in foreach loop:

Now in dataflow task create your data source, add query and parameterize it like that:

In the end it should look somehow like that (what's highlighted in red is inner components of Data Flow Task):

Of course you'll have to add some logging or some other components, but this is basic and will get you moving.

回答2:

You may also what to look into the SSIS tools "For Loop Container" and " Foreach Loop.

You fill a Parameter type Object with a list of values - in my case I used a query in the SQL Task [Lookup missing Orders]. And then the [Foreach Order Loop] task goes through each entry in the parameter and executes the [Load missing Orders] data flow task: