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: When data is being extracted from source systems When data is being transformed 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: ErrorID Entity Record_Id Record Name ErrorDescription DateTime Primary Key of Error Log Table. (System Generated) Unit UnitId Unit 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 Name Data Type Length ErrorDescription Unicode string [DT_WSTR] 500 ErrorLogDate database timestamp [DT_DBTIMESTAMP] —- Entity string [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 Name Data Type Length ErrorLogDate database timestamp [DT_DBTIMESTAMP] —- Entity string [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.  

Change tracking is a new feature available in CRM update 1 that can help in keeping the data synchronized in source and target systems by detecting the data changes since the data was initially created or last synchronized. Change tracking is used for incremental load STEPS: Before we begin to implement change tracking, we need to enable it on the entity for which we need to track changes Customizations → Entity → Enable change tracking We need to maintain a table in Sql so that the output tokens can be saved for future purpose which will help to maintain a version for the records in the form of tokens [Note: The tokens can be maintained in a file also] Let’s start by creating the SSIS package using SSIS integration toolkit for Dynamics CRM-Kingswaysoft Flow of package will be: Drag and drop the Execute Sql task which will be used to fetch the tokens from Sql [Note: In case of initial migration, the token will be NULL] Since initially the table will be empty we need to handle the NULL scenario Create a variable for maintaining the count of rows for the table(we need to store the result set in this variable created) If the count=0 (1st time migration), it goes to a sequence container passing the input token as NULL If the count !=0 , it goes to a sequence container passing the input token as the last inserted token in the table Let’s consider we migrating the data for the 1st time The sequence container will contain a data flow task (having the source and target CRM components) and Execute Sql task (storing the new token to Sql) Data Flow task: Drag the CRM source component Set up the connection, select source type as ‘Entity changes’, mention the entity and define the input and output token variables [Note: In this scenario the Input token will be NULL as it is 1st time migration] Now we drag two CRM destination components CRM Primary Input (Tracking the Updates and Inserts) CRM Secondary Input (Tracking the Deletes) For CRM Primary Input destination component, set up the connection, action as ‘UPSERT’, mention the destination entity and upsert matching criteria For CRM Secondary Input destination component, set up the connection, action as ‘DELETE’ and mention the destination entity Execute Sql task: We need to store the output tokens in Sql, using Execute Sql task In parameter mapping, we mention the input and output tokens variables that we get from the Data flow task Let’s consider the scenario where we already have the data set up The sequence container will contain a Execute Sql task (for fetching the last output token from Sql), data flow task (having the source and target CRM components) and Execute Sql task (storing the new token to Sql) Execute Sql task: We need to fetch the output token from Sql The result set can be stored in the Input token variable which we pass to the CRM source component The steps for data flow task and the execute Sql task are the same as mentioned earlier The final package will look like this: The package can be scheduled on a daily basis thus keeping the data in sync in the source and target systems.  

Note:  This article assumes experience on SSIS Integration Toolkit for Dynamics CRM and understanding of CRM Online Images, Notes and Attachment migration in CRM Online is always a challenging task. One easy way of dealing with them is to use SSIS Integration Toolkit for Dynamics CRM Images Steps: Drag and drop Dynamics CRM Source and Dynamics CRM Destination component. Set up the Source CRM by adding the connection manager, mentioning the source type(Entity /Fetch xml/Entity changes) Set up the Target CRM by setting the connection, choosing the appropriate action (Create/Update/Upsert/Delete etc.), specifying the destination entity Map the Input column and destination CRM field For migrating images, we need to directly map entityimage field of source to entityimage filed of target   Notes/Attachments Steps: For notes and attachments, select the annotation entity as the source as target by following the steps mentioned above The fields to be mapped are subject, notetext, documentbody, filename, mimetype, objectid for mentioning to which entity is the note/attachment related to We use Text lookup function for lookups We can lookup on name/ GUID /custom field as per the need. In the example mentioned above we lookup the accountid which is the primary field for account entity