When developing CRM integrations using SSIS, we frequently need to be able to test the integration logic without actually writing any data into CRM. This type of thing is especially important when there are multiple lookup steps, complex merging logic with other systems, and differential update logic is in place. Within the Control Flow, SSIS gives us the ability to disable an entire data flow task. This is helpful if you need to test looping logic in your control flow, or prevent certain parts of your integration from running. SSIS does not provide a similar mechanism for data flow components. A quick and easy way to halt the package execution before data is written to CRM is to add a Data Viewer prior to the step that writes data to CRM. This works great for small, simple integrations. What if you are dealing with an integration with multiple data flows and multiple places where data is written to CRM? The Data Viewer approach breaks down very quickly and becomes difficult to manage. A better approach to this problem will involve a little more work. As a result of this extra effort you will be rewarded with a much more testable integration and a quick and easy way to "flip the switch" on writes to CRM. The first step is to create a Package Level boolean variable called UpdateCRM and set the value to False. This value of this variable will be used to determine if we will or will not write data to CRM. Next, go into your data flow and insert a conditional split prior to your step that writes data to CRM. Inside your conditional split, create an Output called "CRM Update Control". In the condition box, you can simply drag in the variable we created in the previous step. Your Conditional Split setup should look like this: Connect your CRM write step to the CRM Update Control output of the conditional split. Repeat this process for any other CRM endpoints. You should end up with something like this: Now the entire integration can be run and we can observe record counts between steps, the behaviors of various conditional splits, and evaluate the performance of lookups without having to worry about writing anything to CRM. Notice in the screenshot below, we can see all of the record counts flowing through the various paths of this integration. Also notice that no records are sent to the CRM destination adapters. We can still utilize data viewers to analyze data that is flowing between steps in our integration without having to worry about accidentally closing the wrong one and having data get written into CRM prematurely. When you are ready, you can simply update the value of the UpdateCRM variable to begin writing your data into CRM. This technique can be expanded upon to fit your own needs, depending on the requirements and complexity of your integration. We suggest adding in little things such as this into your integrations to make them more robust, testable, maintainable and flexible.
by
