I am a newbie to SSIS. I have been working through SQL Server 2005 Integration Services and am quite pleased with the book and with SSIS. That said, I am having trouble determining how to handle a challenge.
The business challenge is that we need to push new inventory items that we sell from our back end accounting system onto our web site. The challenge is that each item can show up in 1 or 100 categories. The silly web software wants us to write the item and the categories that item is associated with in one transaction / command.
Stated another way, when we push an item into the web store, we need to push the item and an array of categories in the same transaction.
This is my technical challenge, I cannot figure out how to select 100 records out of an item table in MS-SQL and then create an array of categories for each of those 100 items. (items belong to >1 categories)
I thought I could use two OLE DB sources where the main source was the item table and the 2nd source was the category table.
My best guess at this point is to use an OLE DB source for the item table. Then use the script component and hard code the read from the category list within the script component.
As a note, scalability is not really an issue, there would be no more than 10-20 items being pushed at any given time.
Any help would be GREATLY appriciated.