SSIS Execute SQL Task - multiple sources in sequence and not in parallel

Question

I have a massive SSIS package with an Execute SQL task that reads data from 14 different sources, runs them through a Union All, and then through all the same transformations. Problem is, running 14 massive SELECTs in tandem is choking up the server. I would much rather they run in sequence. But since they all then have to go through the same set of transformations, I really don't want to split them into separate tasks.

Is there any way of configuring the package to execute the OLE DB Sources one at at time (or even two at a time) rather than all at once?

Answer 1

Ideally you would simply change your ETL pipeline to import the data you need into a staging environment and then do your transformations on that data using SQL rather than SSIS activities, as this would be much more efficient and give you much greater flexibility.

That said, one option (and this is a bit of a bodge) that might work would be to daisy chain your Execute SQL tasks with another Success path, so each task flows to both the Union All and (barring the final one in the chain) the next Execute SQL task, kinda like this:

Execute SQL Task 1 -------+
        |                 |
        v                 |
Execute SQL Task 2 -----+ |
        |               | |
        v               | |
Execute SQL Task 3 ---+ | |
        |             | | |
        v             | | |
Execute SQL Task 4 -+ | | |
                    | | | |
                    v v v v
                   Union All ---> Rest of package

---

If you are (understandably) a bit frustrated with the repetitive nature of SSIS development, I would suggest looking into Biml to automate the actual package creation. The linked version 'BimlExpress' is a free Visual Studio add in and a massive timesaver once you are familiar with it.

If you define your transformations as SQL statements, you can supply extract metadata, transformation stored procedures and your destination schema based on these to generate your SSIS project in one go. At the very least you can automate the creation of extract tasks into a staging environment.