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

Question

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.

Evaldas Buinauskas · Accepted Answer · 2017-02-22 10:01:29Z

9

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.

edited Feb 22, 2017 at 10:01

answered Feb 21, 2017 at 18:43

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

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

5 Comments

Leon Over a year ago

I'm actually using Data Flow Task which contains an OLE DB Source -> Flat File Destination. Does your method allow me to save the result set to a CSV?

Evaldas Buinauskas Over a year ago

Yep, it does. I'll amend my answer to be more detailed

Leon Over a year ago

Finally got around to implementing it and it worked well, thanks!

Leon Over a year ago

Just a follow up question if you are still here. Why is it that I have to use 0 for a full result set?

Evaldas Buinauskas Over a year ago

@Leon at first I used some random name, but it didn't work out. I would say that 0 indicates that it's first result set brought by sql statement. If your statement would bring few results, you could indicate which is one using 0, 1, 2, etc...

tember · Accepted Answer · 2017-02-21 18:57:45Z

0

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:

answered Feb 21, 2017 at 18:57

tember

1,5182 gold badges14 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related