SSIS task to handle different destination for each row

Question

My current scenario is that I have HQ pushing changes to branches. There's around 170 branches. The staging table that I'm working with is used to prepare the data from HQ into rows that can be inserted directly into a branch. The issue I'm running into is that each row in the staging table might go to a different branch.

I'm looking for a strategy that lets me loop through the rows in the staging table, and lets me do an insert into a destination from the column that contains the branch id. I'm already familiar with shredding a recordset, dynamic connection strings using variables, and looping containers. The main hangup is in how to change the destination of the current row.

I think this is relatively simple, but I'm not seeing the trees from the forest in this case. Maybe I need to handle this differently?

Answer 1

It never fails. All I needed was to post it publicly somewhere, and the answer came to me, about an hour later. Here's what the overview looks like:

Here are the steps:

1) Create the list of branch IDs and their respective servers, then put it into a Recordset Destination.

2) Loop through the recordset using a Foreach loop task. Map a couple of user variables to store the current branch and server name.

3) Modify the destination connection (listed in the Connection Managers) using the Expressions in the properties editior. Map the ServerName property to the variable that is storing the server name for the current branch.

This sets up a nice framework to handle all of the integration. I can now do anything I need to do inside the Foreach Loop Task.

Hope that helps somebody!