Complex SSIS Data Extract...I need Help Please

Question

So here's the scenario...

1. Extract [DynamicsCRM] Lead Information
2. Determine if that Lead is a Repeat Lead...Meaning the individual has attempted to Re-Enroll...or if it is a fresh/new lead
3. Use [DynamicsCRM] Data to Lookup against our Customer Database that is on another Server and Database
4. If it is determined that the [DynamicsCRM] Lead is in our Customer Database, then there are three scenarios

4.1 The Lead is truly a Lead and does not yet exist in our Customer Database so we want to extract that to our Pre-Enrollment File
4.2 The Lead is in our Customer Database and the DateTime Stamp of the Customer Database Row is greater than or equal to the DateTime Stamp from [DynamicsCRM] Lead so we want to extract that to our Pre-Enrollment File
4.3 The Lead is in our Customer Database and the DateTime Stamp of the Customer Database Row is less than the DateTime Stamp from [DynamicsCRM] Lead so we do NOT want to extract that to our Pre-Enrollment File because it looks as though the individual is trying to Re-Enroll and it is not a "fresh", "new" Pre-Enrollment record

Now I have tried doing this with a Lookup task matching first for the [DynamicsCRM] Lead found in our Customer Database and the DateTime Stamp of the Customer Database Row is greater than or equal to the DateTime Stamp from [DynamicsCRM] Lead so we want to extract that to our Pre-Enrollment File...Scenario 4.2

My problem is the Non-Matches.

For the life of me, I cannot figure out how to do another Lookup of the Non-Matches. SSIS does not seem to allow me to do a Lookup from a Lookup.

So how can I handle Scenarios 4.1 and 4.3 above?

I hope this is clear. I am racking my head around this trying to come up with a solution in which I can try and attempt to do another Lookup from the [DynamicsCRM] Data to our Customer Table. To add even more confusion, our [DynamicsCRM] resides on a Microsoft Azure SQL Server and our Customer Database resides on a VM Server....although that's really not a big deal since we are using our Connection Managers to manage that.

I hope the question and scenario are clear enough.

I appreciate your review and am hopeful for some feedback.

Thanks in advance for your review and feedback.

Answer 1

What Dynamics CRM connector do you use?