Conditional data flow task in SSIS

Question

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:

1. 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.

2. 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 ?

• billinkc: I choose to get reports about cats so my data flow is going to send down cat attributes (fur color, name, tail length). Jason prefers iguanas so does he gets an entirely different set of attributes in his data flow (eye ball size, weight, tail length)? Or, is the data all mashed into the same buckets which then get fed to reports?
• mmarie: What version of SQL Server are you using? This sounds like something that can be accomplished with SSRS data-driven subscriptions: msdn.microsoft.com/en-us/library/ms169673.aspx

Answer 1

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.

• Yograj Bere: I added the DF containers...but those DF containers are sharing same excel destination (Single excel, different sheets..). So I get "SqlDumper.Exe" error intermittently...Seems like one DF locks out excel for the other DF....Is there any way that all DF will work sequentially, one after another & still obey the condition that we have put on precedence conntraint...?
• SQLRNNR: No. What I think I would do in that case is have each DF go to a separate Excel workbook. Then a task at the end to combine all of them into a single workbook.