I've completed the SSIS tutorial on microsoft's web site and pretty much duplicated it inside my first project as it completely fits. I need to extract from a flat file source, lookup student; instructor; test and date records in DIM tables and finally write out testScores and Agregates to a few FACT tables. I've created all the connection managers I need and created the flat file source and lookup objects in my data flow task. At this point I'm forced to veer from the tutorial as I need to run a query against another database to obtain Student and Instructor data when they don't exist in the appropriate DIM tables. Can anyone provide a list of source, transform and or destination objects I would need to accomplish the following: If Student doesn't exist in DIM table Then Query production database for student bio information using result set from query above, insert new record into dimStudent End If Continue processing flat file. Thanks in advance

use the lookup transform a) Drag a Data Flow Task in conrol Flow. b) In the DFT do below c) The matched output will update the content Abhinav http://bishtabhinav.wordpress.com/

AB82, thanks for your reply and forgive my frankness but I'm pretty sure you've over simplified my problem. When student lookup fails I need to query our production db and build a student record in accordance with the dimStudent table design. I can't write to a dbDestination as you've described until I obtain the field values which describe a given student within dimStudent table. Anyone else have any recommended transformation objects to be placed between my lookup/fail and AB82's dbDestination object? I've attached a pdf describing my case in detail. I'm programming and learning at the same time and I'm reading about containers and groups (conceptual knowledge only) - perhaps I should encapsulate the student lookup/add-to-dimTable-if-missing process into a group or container, then create another group or container for the instructor lookup/add-to-dimTable-if-missing and so on until all dimTables are populated and accurate at which point I write the score data to the factTables. My Case Thanks to anyone willing to take me on as a case

You'll need to use a couple more Lookup components to retrieve the needed values from your production DB. I can't say exactly what those will need to look like - but Lookups (probably configured as NO cache) are most likely what you'll need. Talk to me now on