tags:

views:

51

answers:

1
Q: 

How do I create a "two pass" package in SSIS to get extra details?

I need to create an SSIS package that will do the following:

  1. Connect to Service A and download recent activity records. These records contain an User Id and an activity code.

  2. Iterate through the results of the result set from Service A and connect to Service B retrieve additional details for each User Id (name, department, etc.).

  3. Put all of the results from this "two pass" scenario into a DataTable on our SQL Server.

I'm able to do parts 1 and 3 without issue, but I'm having difficulties in how to implement part 2.

Do I put the results from part 1 into the destination table and then iterate through the results in a second Data Flow Task? Or is there a way to "append" the results from steps 1 and 2 into a single table/file/etc. and push the results into the destination table all at once?

I'm seeing all the controls available in BIDS, but can't seem to make them fit.

+1  A: 

In the dataflow tab:

  1. Use 1 of the dataflow sources (XML source)
  2. Use the Lookup Transformation
  3. SQL Destination
ZippyV  2009-07-29 23:16:07
Do I target the Lookup Transformation to my destination database or to an intermediate column before pushing it through to the end.?
Dillie-O  2009-07-29 23:26:54
Yes, Datasource(1) connects to Lookup Transformation(2) connects to SQL Destination(3)The Lookup component adds any new columns you need before sending it to the SQL Destination.
ZippyV  2009-07-29 23:52:21
I think this is going to do the trick. I was digging into this and noticed that you can specify new columns as opposed to existing ones in the output. I'll give this a whirl and update accordingly.
Dillie-O  2009-07-29 23:57:33
This did it! I changed things slightly to use a Script Transformation since I was calling a web service, but the process worked out the same. Thanks!
Dillie-O  2009-07-30 16:30:20

related questions

iterative version of easy recursive algorithm
Existing LINQ extension method similar to Parallel.For?
How to modify or delete items from an enumerable collection while iterating through it in C#
modern for loop for primitive array
Converting a List of Tuples into a Dict in Python
iterative version of recursive algorithm to make a binary tree
Why is it bad to use a iteration variable in a lambda expression
What is the easiest/best/most correct way to iterate through the characters of a string in Java?
Is Iterator initialization inside for loop considered bad style, and why?
.NET Fastest way to iterate through rows in a datatable?
Way to go from recursion to iteration
Team size and project iteration length.
Is it correct to use the backtick / comma idiom inside a (loop ...)?
Is there a zip-like method in .Net?
How do I iterate a .Net IList collection in the reverse order?
In C# .NET 2.0, what's an easy way to do a foreach in reverse?
Iterators in C++ (stl) vs Java, is there a conceptual difference?
Iterate Over Map
Lisp list iteration
Why does Python's iter() on a mapping return iterkeys() instead of iteritems()?
Replacement for for... if array iteration
Generic iterator
What's the safest way to iterate through the keys of a Perl hash?
How do I use Python's itertools.groupby()?
Using 'in' to match an attribute of Python objects in an array