Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 excute 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 to implement this conditional data flow in data flow task ?

share|improve this question
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 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 at 21:13

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

share|improve this answer
    
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 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 at 1:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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