20 Aug’19

Blog, Dynamics CRM, Others, UncategorizedCreating Custom Error Log Table in SSIS for OLEDB Block And Kingswaysoft Dynamics CRM Block

Introduction

This log error will help us to understand and fix an issue as quickly. There are three main phases in an SSIS ETL execution life-cycle to catch errors:

  1. When data is being extracted from source systems
  2. When data is being transformed
  3. When data is loaded to the target systems

Customized Error Handling also avoids the failure of Package during Runtime. It allows the package to be executed successfully and the Errors can be checked later from the Customized Error Log Table which you create to know what problem exactly occurred.

Our Scenario

For demonstration purpose, we will consider a Units Integration Map for Integration of Units from SQL to Dynamics CRM.

In this Blog, we will Create a Customized Error Log Table in SQL and Catch the Errors from SQL (Source Block) and Dynamics CRM (Destination block).

The Map for Unit Integration is as follows:

Customized Error Handling in SSIS

Execute the following query and create a customized Error Log Table in SSIS:

CREATE TABLE [dbo].[ErrorLog](

[ErrorID][uniqueidentifier] NOT NULL default newid() primary key,

[Entity] [varchar](250) NULL,

[Record_Id] [int] NULL,

[RecordName] [varchar](250) NULL,

[ErrorDescription] [varchar](500) NULL,

[DateTime] [datetime] NULL

) ON [PRIMARY]

The structure of Table is as follows:

ErrorIDEntityRecord_IdRecord NameErrorDescriptionDateTime
Primary Key of Error Log Table. (System Generated)UnitUnitIdUnit Name Error Message Error Log Date

Error Handling at OLEDB Source Block:

Step 1:

Add a Script Component to catch the Error Description, Error Log Date and Entity.

Select Transformation and click on Ok.

Step 2:

Connect the Error Output (Red Arrow) from OLDEB Source to the Script Component. Select “Redirect row” for all columns in the Error and Truncation Columns to redirect Error Output to Script Component.

Step 3:

Configure the Script Component as following:

In Input Columns section Select Error Code and Error Column.

In Inputs and Outputs section Add following Columns

Column NameData TypeLength
ErrorDescriptionUnicode string [DT_WSTR]500
ErrorLogDatedatabase timestamp [DT_DBTIMESTAMP]—-
Entitystring [DT_STR]50

In the Connection Managers Section add a new connection and select your SQL connection

In the Script Section click on Edit Script. After a minute a New Editor Window will Open. Here you have to copy and paste the following Script inside the “public override void Input0_ProcessInputRow(Input0Buffer Row)” section.

Code Snippet:

try

{

Row.Entity = “Unit”;

Row.ErrorDescription =

this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

Row.ErrorLogDate = DateTime.Now;

//If an error occurred due to Check Constraint, the ErrorColumn would be 0,

and that error affects the entire row. Hence there is no specific column for

that error

 

if (Row.ErrorColumn == 0)

{

Row.ErrorDescription = “An error that affects the entire row”;

}

 

//If an error occurred due to Data type, then errorcolumn name would be

updated.

 

else

{

var componentMetaData130 = this.ComponentMetaData as

IDTSComponentMetaData130;

if (componentMetaData130 != null)

{

Row.ErrorDescription =

componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn).Replace

(“Customer Target.Inputs[OLE DB Destination Input].Columns[“, “”).Replace(“]”, “”);

}

}

}

 

catch (Exception ex)

{

Row.ErrorDescription = “Unable to get Error Column Name”;

}

Click on Save and then Close the Window.

Step 4:

Add a Data Conversion Block to avoid any Truncation Errors because of Data Type Conversion between NVarchar and Varchar Data Types of the Error Description Column.

Select ErrorDecription Column and select Data Type as String. Click on OK.

Step 5:

Add an OLEDB destination block.

Configure your OLEDB Connection Manager and Select the Error Log Table which you had created in SQL Server.

In the Mapping section do the following Mappings and click on Ok.

Error Handling at Dynamics Destination Block:

Step 1:

Perform the Steps 1 and 2 as specified above in Error Handling at OLEDB Source Block.

Step 2:

Configure the Script Component as following:

In Input Columns section Select Error Code, Error Column and CrmErrorMessage.

In Inputs and Outputs section Add following Columns

Column NameData TypeLength
ErrorLogDatedatabase timestamp [DT_DBTIMESTAMP]—-
Entitystring [DT_STR]50

In the Connection Managers Section add a new connection and select your CRM connection:

In the Script Section click on Edit Script. You have to copy and paste the following Script inside the “public override void Input0_ProcessInputRow(Input0Buffer Row)” section.

Code Snippet:

Row.Entity = “Unit”;

Row.ErrorLogDate = DateTime.Now;\

Click on Save and then Close the Window.

Step 3:

Add a Data Conversion Block to avoid any Truncation Errors because of Data Type Conversion between NVarchar and Varchar Data Types of the Error Description Column.

Select CrmErrorMessage Column and select Data Type as String and length as 500 i.e. according to the length of columns SQL. Click on OK.

Step 4:

Add an OLEDB destination block.

Configure your OLEDB Connection Manager and Select the Error Log Table which you had created in SQL Server.

In the Mapping section do the following Mappings and click on Ok.

Checking the Error Occurred during Integration

You can see the Error rows passing through the Error Output and being logged in our Error Log Table.

Open the Error Log Table to check the Errors Occurred.

Now you can easily identify the errors occurred during Integration process from your Custom Error Log Table and solve them to have successful Integration results.

 

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

  • Introduction to Microsoft Dynamics Commerce Tools

    19 October’ 2019

    As we know, Dynamics 365 Commerce is released in the preview version, So in this blog, we will have a quick explanation ...

    Read more
  • Error Handling Approaches for Integration

    16 October’ 2019

    Introduction: Handling Errors is a best practice in Integration when integrating to keep a track of the Errors that occu...

    Read more
  • Change in URLs for Business Central Tenants

    16 October’ 2019

    Change in base URLs for Business Central SaaS Tenants. I have noticed the Business Central links for Tenants have chan...

    Read more
  • Record Deletion Tool in Business Central

    16 October’ 2019

    Introduction: After a successful Go-Live in Business Central, we somehow need to delete a record from Backend. There is...

    Read more
  • Error “A reference to ‘xyz ‘ is required to compile this module” solution

    10 October’ 2019

    Many of the time while building project/solution we came across the “reference is required to compile this module ...

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