12 Sep’19

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

Part 4/4

Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic

Introduction:

To handle the scenario where the Invoice Details fail to get Integrated or Incomplete Integration of Invoice Lines the Invoice itself should get deleted from CRM as we don’t want to Integrate Incomplete records.

Step 1:

Add a DFT to Delete Invoice in the Control Flow as follows:

The Map after Completion looks like the following:

Step 2:

In our case, we are Inner Joining Invoice and Invoice details Table based on OrderNumber and Invoice Details and Products Table based on ProductID. This will help us to retrieve the columns which are not provided in a single Table and are required for Integration of Invoice Details.

Add an OLEDB Source block with the required query and configure it as follows:

Click on Parameters to add a Parameter as follows:

Step 3:

We have a Ported field in our Data Base which set to 1 once Integrated with CRM.

Add a Conditional Split Block to Split into two categories based on the sum of ported and count of ported as follows:

Count of Ported = Sum of Ported

This indicates that all the Invoice Header and all its Details have been Integrated Properly. In this case, we will add an OLE DB Command Block to update the status of Invoice Header to 1 as follows:

Count of Ported != Sum of Ported

This indicates that all the Invoice Header and all its Details have not been Integrated Properly. In this case, we will add an OLE DB Command Block to update the status of Invoice Header to 0 just as above where we update it to 1:

Once the Status has been Updated we have to Delete the Faulty Invoice Record from CRM.

We have performed Lookup based on Invoice Number as follows:

Output:

Source (SQL Server)

Invoice Header-

Invoice Details-

Execution of Package –

Destination (CRM)

We can see that one Invoice is Integrated which had all proper data. The Other Invoice which failed to Integrate in some aspects has been automatically deleted from CRM.

Note:

This is the fourth part of the 4 part blog series.

Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration.

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

  • How to create Dynamics 365 Finance & Operations (formerly Dynamics AX) connection in SSIS

    12 December’ 2019

    Introduction: Creating a Connection is one of the first Steps during the integration of data. While performing Integrati...

    Read more
  • How to create AX (D365 F&0) Connection in SSIS

    12 December’ 2019

    Introduction: Creating a Connection is one of the first Steps during integration of data. While performing Integration w...

    Read more
  • First cut review of new D365 Project Service

    12 December’ 2019

    The new Project Service coming up is a much-advanced version of PSA. Even more, than it will be called as Project Servic...

    Read more
  • Business Central Wave 2 Features – Section 4

    11 December’ 2019

    Introduction: In this blog, I will be attempting to provide information about the new Modern Client features of Business...

    Read more
  • Business Central Wave 2 Features – Section 5

    11 December’ 2019

    Introduction: In this blog, I will be attempting to provide information about the new Modern Client features of Business...

    Read more

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close