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.

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.

 

 

 

 

 

 

 

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

  • POS Receipts printing, Device connectivity issues in Microsoft D365 For Retail

    16 September’ 2019

    Activating/installing POS on a new system is a straight forward process. However what I have noticed on several occasion...

    Read more
  • Using For Each Loop in SSIS for Invoice Integration from SQL Server to Microsoft D365 Sales Using KingswaySoft Connector for CRM: Part 1

    12 September’ 2019

    Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Introduction: The SSIS ForEach Lo...

    Read more
  • Using For Each Loop in SSIS for Invoice Integration from SQL Server to Microsoft D365 Sales Using KingswaySoft Connector for CRM: Part 2

    12 September’ 2019

    Phase 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM: The Map after Completion will look ...

    Read more
  • Using For Each Loop in SSIS for Invoice Integration from SQL Server to Microsoft D365 Sales Using KingswaySoft Connector for CRM: Part 3

    12 September’ 2019

    Part 3/4 SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Step 1: Add a DFT to Integrate the In...

    Read more
  • Using For Each Loop in SSIS for Invoice Integration from SQL Server to Microsoft D365 Sales Using KingswaySoft Connector for CRM: Part 4

    12 September’ 2019

    Part 4/4 Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic Introduction: To handle the...

    Read more
  • We respect your privacy.
  • This field is for validation purposes and should be left unchanged.