What are my options?
Is it possible to reference output columns of the multicast task in something like SQL Query for Ole DB Destination?
Thanks in advance.
- Nitesh Ambastha
-Jamie
Jamie Thomson wrote:
Nitesh,
You need a conditional split transformation on each of your data paths after the multicast transform. Conditional Split transforms are effectively SSIS's WHERE clauses.-Jamie
Thanks Jamie.
My feeling is that 'Conditional Split' helps for WHERE clauses that operate on data value. For example, ...WHERE order_date < [COLUMN1]
But, can I perform nested queries? For example, WHERE [COLUMN1] NOT IN (SELECT COLUMN_A, COLUMN_B FROM Table_Y)
I wasn't specific about this need in my earlier question and I apologize.
Getting more specific about my current need, from the output records of the multicast, I want to INSERT only those records in an Ole DB Destination that are not already present in the destination table (identified by a combination of two columns present in the multicast output). Any idea how to acheive this?
Additionally, I would prefer to update the records from the multicast source for which I already have data in the Ole DB destination.
Thanks again.
Nitesh Ambastha
nitesh.ambastha@csfb.com
Maybe using a combination of Look Up and Conditional split will work.
Suppose you want to split on WHERE [Column1] NOT IN (SELECT [ColumnA] FROM Table_Y)
If you first LookUp ColumnA in table Table_Y on Column1 == ColumnA and put it in a dummy column, say [Dummy], this will result in Column1 and Dummy having the same value if the row does not comply to the where clause and it will have the value null if it does.
Next you split on Dummy==null and you have your result.
I didn't try it myself but I hope it helps.
Regards,
Henk
Nitesh,
You need a conditional split transformation on each of your data paths after the multicast transform. Conditional Split transforms are effectively SSIS's WHERE clauses.-Jamie
Thanks Jamie.
My feeling is that 'Conditional Split' helps for WHERE clauses that operate on data value. For example, ...WHERE order_date < [COLUMN1]
But, can I perform nested queries? For example, WHERE [COLUMN1] NOT IN (SELECT COLUMN_A, COLUMN_B FROM Table_Y)
I wasn't specific about this need in my earlier question and I apologize.Getting more specific about my current need, from the output records of the multicast, I want to INSERT only those records in an Ole DB Destination that are not already present in the destination table (identified by a combination of two columns present in the multicast output). Any idea how to acheive this?
Additionally, I would prefer to update the records from the multicast source for which I already have data in the Ole DB destination.
Thanks again.
Nitesh Ambastha
nitesh.ambastha@csfb.com
Nitesh,
I'm not sure I quite understand what all the multicasts are for but it sounds as though your basic requirement is to find out whether a record already exists in the target or not.
There are 2 methods of doing this and are documented here: http://www.sqlis.com/default.aspx?311 (although the SQLIS.com site seems to be down at the moment)
Any questions...give us a shout.
-Jamie
