Solved

SSIS Package design question

Posted on 2011-09-05
1
201 Views
Last Modified: 2012-05-12
Hi,

Would you please let me know what woud you do if the followings are need to be implemented using SSIS 2008 BIDS:

1. Query a database table A
2. Loop through each A record, and query another database table B with part of A fields
3. Based on selected fields of B, set user variables accordingly.
    At the same time, loop through each B record, and query the third table C with part of A and B fields.
4. Concatenate a string with selected fields from Table A, B and C with the variables
5, Write the string to a flat file

please, if possible, illustrate the nodes that you would use. Thanks a lot.

I have tried to use 2 ways, but still not able to make them to work:

1. Data Control  with OLEDB Source for A table --> OLEDB Comand for B --> Script Component

with this way, OLEDB Command for B is not able to produce outputs. Therefore, I am not able to set variables in side script based on the selected B records.

2. Using Control flow -->Execute SQL Task for A --> Foreach Loop for resultset of A --> contains another Execute SQL Task for B --> still inside the foreach container, using Script Task

With this way, I can have outputs of precedent nodes however, I am not able to referent any resultsets in side the script.

Hope you understand what my problems are.

Thanks,
0
Comment
Question by:mystudent
1 Comment
 
LVL 2

Accepted Solution

by:
awarren85 earned 500 total points
Comment Utility
Hi.

Pretty tricky.  Could this be done using pure SQL code and creating a view perhaps?  Maybe using some temp tables?  Probably a better knowledge of how tables A, B, and C work with each other would help.  Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now