Brief Architecture:
I am creating a SSIS package to generate Excel reports. There are different types of reports, So I have created a table to track each user request for report generation and tracking the status of request. A Sql job picks each request and generates the excel file on FTP server.
Problem:
-
In control flow, I am executing select on "ReportRequests" table and iterating recordset using a For Each Container. I have created a variable named "ReportType" to get column value from database.
-
In Data flow task, on basis of "ReportType" variable, I want to execute different queries on different databases to generate report. I will have 3-4 different ado.net sources with different queries. But those should work on basis of value of "ReportType"
How can I implement this conditional data flow in data flow task ?
Best Answer
You can create 3-4 different data flow tasks within your control flow (inside the Foreach loop container).
Place a sql script before the DF containers. You can have the script actually do something or not. The idea is to create a conditional from this task that leads to the 3-4 data flow tasks.
On the connector for each DF object, add a constraint that the variable == "somevalue". Since each Connector has a different report type value, only one will execute.