Exploring SSIS

I have started exploring SSIS

My Requirement is exactly like Data Driven Query Task in SQL 2000 DTS

Scenario :

1. I Have a Source & Destination Table. Let's say S- Source Table, D - Destination Table.

2. I want to insert rows from S table to D table which are not available in table D

3. I want to update the rows in table D for which I have the records in both the table S as well as D

4. I can make use of a column called 'C1' to find out the rows exists in destination table or not

5. I want to Track/Trap the source as well as destination error rows.

I achieved the functionality as follows.

1. I Added OLEDB Source to fetch the record from table S (Source) (Configured to direct error output to "Redirect Row")

2. On an error or failure at source event I am directing the row to "OLEDB Command " (I will get rows failed at source

3. I added Row count transformation to find out the rows failed & source & I populated the value in package level variable

3. On Success - I added a lookup Transformation

4. On Failure (We need to trigger Insert) the two folows I added 2 different "OLEDB Commands" for insert & update.

5 Subsequent to Both of OLEDB Command transformations I added row count transformations to get the rows succeeded & failed at destination through 2 differnet row count transformation for each of OLEDB Command transformation. It serves my purpose. but I am not sure whethere this is the efficient way of doing this or there is any alternative avaiable to the same

Come on over and post this in the SSIS forum...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1