Using Conditional Split Transformation in SSIS

Recently we had a requirement to Upsert Customer’s Spend related information from SQL DB to our CRM Online.

We had the spend captured per brand wise in the DB as shown below.

And in our CRM, we had one custom entity that had fields corresponding to each of these Brand as shown below

i.e. one section for each Brand and inside that section 5 fields corresponding to the columns in the SQL Table.

The GCR column of the table was the Alternate Key column in our custom entity which we used for Upsert.

Now to make sure we update only the corresponding Brand fields in CRM, we can make use of Conditional Split Transformation component.

Just after OLE DB source, add a Conditional Split component

Create one output per brand i.e. (select distinct Brand from the source table records) as shown below

Now add a CRM Destination Component and bind it to the corresponding output

And inside Columns section, map only the corresponding field of the selected Brand.

The finished package à

Hope it helps..