Part 1/4:

Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration.

Introduction:

The SSIS ForEach Loop container will repeat the control flow task for N number of times, which is similar to Foreach loop in any programming language. This concept can be used for Integration of Header and Lines concept like Invoice.

Our Scenario:

For demonstration purpose, we will consider an Invoice Integration Map for Integration of Invoice and Invoice Details from SQL to Dynamics CRM.

In this blog, we will use for Each Loop to Loop over all Invoices present in SQL Server DB and their respective Invoice Details.

The Map for Invoice Integration after Completion will look like following after completion.

Integrating Invoice

Step 1:

Create an execute SQL task which will count the Number of Invoices present in SQL Server DB having Ported Status marked as Zero.

Create variable CNT having Data Type INT32 with initial value 0 to store the count of Invoices. Configure the following settings for the Control Flow Task.

Step 2:

Add a Sequence Container and double click on the green arrow to apply a condition as follows to proceed only if there are any Invoices are present in the DB else skip the Sequence Container.

Step 3:

Add an Execute SQL Task to store the all InvoiceNumbers in object variable.

Create variable InvoiceNumbers having Data Type Object to store the InvoiceNumbers. Configure the following settings for the Control Flow Task.

Step 4:

Add a ForEach Loop Control Flow Task and configure it as follows to Loop over Invoices for Integration purpose.

In the Collection Section select Foreach ADO Enumerator for Enumerator type and Rows in the first table as the Enumerator Mode.

Create variable InvoiceNumber having Data Type String to store the InvoiceNumber for a single record to be fetched in one cycle. Configure the following settings for the Control Flow Task.

Step 5:

Add a DFT to Integrate Invoice Header.

Note:

This is the first part of the 4 part blog series. Here are others:

Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM

Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM.

Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic.