So, I have a scenario using SSIS in which I have completed the first half:
Create a SSIS Package that calculates the Total and Avg Due Amounts by SalesPersonID and stores the aggregated values to a Destination table and flat file.
Now, the following is what I have a few issues with:
Once the flat file is available, the package should loop through the record collection, retrieve Sales Person Contact Info and YearToDate Sales for each SalesPersonID, and writes those records in a separate flat file for each SalesPersonID.
FYI: I'm using the AdventureWorks DB
My solution (so far) is to utilize a Foreach Loop Container in combination with a Script Task. But I'm not sure of Visual Basic script to retrieve the full SalesPerson info.