jessiedev


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.




Re: How to de-normalize one to many relationships?

JayH


It sounds to me like you need two dataflows: the first to insert the new categories, and the second to lookup the new categories and insert the items. This can be done in a single transaction by setting RetainSameConnection=True on the connection manager for your destination connection, and placing Execute SQL Tasks before the first dataflow and after the second to execute BEGIN and COMMIT TRANSACTION statements respectively.





Re: How to de-normalize one to many relationships?

jessiedev

JayH - Thank you for the reply. My original post was not as clear as it should be.

First, our destination is actually a web service which we are accessing via a proxy / assembly. We call the assembly from a reference inside the script component. The goofy web service wants us to write the item record along with the categories to which the item belongs. The categories is basically an array that has to be declared.

Second, the categories are already defined on the web site. Each category has its own category ID. When we write the item record, we reference an array of category id so the web site software understands the relationship between the item and the category.

From a pseudo code perspective:
1) Select items from the item table.
2) Process each item one at a time.
3) For each item, find the associated category id's
4) Count the number of category id's for each item
5) Declare an array of category id's for each item using the count from #4 above
6) Populate the array with the category id's
7) Populate the "item record" with the category id array.
8) Call the web service via the assembly

Yes, the web service I am forced to use is a bit goofy but I cannot change it. :-(

Thank you for any thoughts...





Re: How to de-normalize one to many relationships?

JayH

Okay, I have a better picture now. I'm still not sure about where the item/category relationship is coming from, but if you can get the rows in an order like the following, then you should be able to create the array pretty easily in script.

Item 1 Category 1
Item 1 Category 2
Item 1 Category 3
Item 2 Category 3
Item 2 Category 2

If the data enters the script sorted by Item, then the script can evaluate each row and build an array of Categories for each item. When the Item changes from the previous row, you know you have all the Categories and you can make your web service call. The last row condition would need special handling and for that you should override the FinishOutputs method in your script so you can call the web service for the last Item.

Let me know if that sounds closer to what you're looking for.




Re: How to de-normalize one to many relationships?

jessiedev

JayH,

Yes, you have done a better job of explaining my issue that I have. I believe I comprehend the concept.

I am working on your concept now.

Be back later today. With me luck!

I am so remedial in SSIS...