12 Sep’19

Blog, D365 Sales, Dynamics 365, UncategorizedUsing For Each Loop in SSIS for Invoice Integration from SQL Server to Microsoft D365 Sales Using KingswaySoft Connector for CRM: Part 1

Part 1/4:

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


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.


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.








Written by

Rushank Karekar

Software Engineer

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Time zone Conversion from Microsoft D365 for SSRS Reporting

    6 November’ 2019

    Introduction: Converting Date/Time values according to a time zone is quite challenging task in SSRS Reporting. For D365...

    Read more
  • How to Synchronize Office 365 and Zoho People?

    4 November’ 2019

    If your organization is using Zoho People and Office 365, you will find this article very useful. In this article we are...

    Read more
  • Check Plugin Timeout issues using Tracing in D365 CE

    31 October’ 2019

    In Dynamics plugin implementations, you might have lengthy complex plugins that makes calls to Dynamics several times. I...

    Read more
  • Embed Secure Power BI report using Python Web Application with Flask in Visual Studio 2015

    31 October’ 2019

    In this article, we will embed a Power BI report in a python web application with flask in visual studio 2015. Following...

    Read more
  • Automate sending of Survey (Recurring) – Microsoft Forms Pro

    28 October’ 2019

    You might be having some requirements for automatically sending Microsoft Forms survey in recurrence like there might be...

    Read more