SSIS - Best way to accumulate data from several related tables before insertion into another database
Posted on 2010-11-08
I'm a newbie to SSIS. I have to move data from a vendor supplied database to one that we will use for reporting. The vendor database does not store all information at the transaction level that we will need to query. For example: We have customers assigned to certain sales reps as their default sales rep. The transaction data contains the actual sales rep that handled the transaction, but not the default sales rep for that customer. Our sales managers need to run reports that show sales grouped by default sales rep as well as the person who handled the transaction. They need these reports so that they can reassign the default sales rep periodically to balance work loads.
So, my problem is that I need to offload all transactional data from the vendor's database to a reporting database and capture various other pieces of information that would be a snapshot of the related tables' data at the time of the transaction. There are several pieces of data about both the item being sold and the customer that are not stored by the vendor's database as part of the transactional record that may change over time. I cannot alter the vendor's database. I must capture the value of these data elements on the day of the transaction.
I'm working in SSIS, and this is my first attempt at creating an SSIS package. We are running SQL Server 2008. Any guidance from the experts is appreciated.