4

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 ?

Improve this question
2
  • 1
    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?
    – billinkc
    Jul 31, 2014 at 19:48
  • 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
    – mmarie
    Jul 31, 2014 at 21:13

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • 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...?
    – CodeTody
    Aug 4, 2014 at 17:24
  • 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.
    – SQLRNNR
    Aug 5, 2014 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.