Recently we came across a situation in SSIS where we wanted to lookup for a certain value and based on that we had to divert rows from previous step to next destination and this had to be done all in a single dataflow task with multiple records coming from previous step. Usually we go for the foreach loop container and multiple dataflow tasks when dealing with processing multiple records. But looping component then impacts speed as we had millions of records to process from our Dynamics CRM. Also we know that we can’t include any of the source component in between, as Source components do not take any input(s) (neither SSIS nor Kingswaysofts Dynamic CRM Source component).
When we were such stuck-up we stumbled upon lookup component. Lookup component allows to look for and get data from related records. Let’s take an example to understand this. We had Admission entity which had relation with our Student(Contact) entity and we had to look for Admission Number from Admission in Student and based on matching record either Create or Update Student record. To do this, we did following steps:
Added a dataflow task and named it as Process Admission Records
Double click Process Admission Record and go to Dataflow tab. Added Dynamic CRM Source component and named it as Get Admission where we get Admission entity records.
Get all the needed Admission entity fields by editing the Get Admission Source component as follows:
Now add the lookup control and connect the Get Admission Source output to lookup control. Edit the lookup control and set following properties.
Select “Redirect rows to no match output” so that we can make use of it later to redirect rows as per need.
Next in Connection tab create an OLEDB connection to the CRM database and select the table (in our case it was Contact(Student) table).
Next in Columns tab we get the Available Input columns at left hand which came from the previous Get Admission step and Available Lookup Columns at right hand which are from the Contact table we connected to in Connection tab. We just connect the matching column from both on which the left column records need to be looked up or matched with right.
Also we select the Output columns from the Contact table from right which we want to get as output if the matching record is found. Say “OK” finally and we will get two outputs from lookup control as you can see in below figure.
As you can see we got two outputs as one for Lookup Match and Lookup No Match. Based on our conditions, we connected Lookup Match to Update Students(Contact) record based on ContactId we selected as output from lookup component as shown in earlier figure. Similarly connected Lookup No Match to Create Student(Contact) record Dynamics CRM destination component of kingswaysoft.
Note: We can use lookup component in case of Dynamics CRM only if we have it as On-Premises or IFD in which we get access to Dynamics CRM database.
Thus, we can query for another entity in between components without breaking the dataflow.
Maplytics August Release…Coming Soon!