I was recently tasked with importing sub nodes of a large XML file as notes (annotations) against matching parent records in Dynamics CRM. This blog provides a step-by-step guide on how to achieve this.

This is part one of a two-part blog series. In this post, we will be focusing on steps 1 & 2 in the overall breakdown described below.

The goal can be broken down into these smaller steps:

  1. Create an SSIS “For Each Loop Container” to get the file name of the large XML file for processing.
  2. Create an SSIS Script Component of type “Source” that will read the large XML file and break it into smaller nodes (at the Notice level, see below for an example) that will be stored against the parent records (also to be created).

 

  1. Add an SSIS Derived Column Transformation to add some additional metadata fields to our dataflow activity, i.e.
    1. The name of the XML file being imported &
    2. The Date of the file being imported (which is included in the file name)
  2. Creation of a parent record that will be associated with the xml node (CRM note) record. The name of the parent record will be BillXML.
  3. Creation of the note.

 

Now that we have a plan, it’s time to put it into action!

Step 1: Create an SSIS “For Each Loop Container” to get the file name of the large XML file for opening.

  1. Drag the Foreach Loop Container onto the Control Flow
  2. Provide the required file information by filling out the Folder and Files parameters:

foreach-loop-editor

Note:  The “Files” text input allows for wildcards. A “*” means any number of any characters, whereas a “?” substitutes for any single character. The loop container will loop through each file that is matched.

  1. To put the name of the file being processed into a variable, you will need to go to the variable mappings tab and add an entry like follows (Note: you will need to create a user variable first, I have called the variable FileBeingProcessed):

foreach-loop-editor-file-being-processed

 

  1. Now we have a loop container and are able to retrieve the name of the file being processed iteratively, it is time to add a “Data Flow Task” in the loop container (it will be executed once for each file). Drag a “Data Flow Task” into the loop container.
  2. We are now finished Stage 1. You should have something that looks like the following:
xml-loop-container

Figure 2 – A For Each Container containing a Data Flow Task

 

Step 2: Creating an SSIS Script Component of type “Source” that will read in the large XML file and break it into smaller nodes (at the Notice level, see below) that will be stored against the parent records (also to be created).

  1. Double click the Dataflow task to open it.
  2. Add a new Script Component. When prompted select “Source” as its type.
  3. Add the following read only variable to the script as follows:

script-transformation-editor

 

  1. Click on the Input & Outputs column and create new columns as follows (by clicking the Add Column button).

script-transformation-editor-2

  • DocumentBody = Unicode text stream *This will allow us to store large XML segments as notes and is the focus of this article
  • Subject = string [DT_STR], length = 100
  • FileName = string [DT_STR], length = 100
  • MimeType = string [DT_STR], length = 100
  • ObjectTypeCode = four-byte unsigned integer
  • MDateOfFile = date [DT_Date] (Note: the prefix M is for Metadata)
  1. Click the Edit Script button and add the following code to PreExecute. This sets the newly created variable fileBeingProcessed  to the input read-only variable User:FileBeingProcessed .

 

  1. In the CreateNewOutputRows method, add the following code:

 

  1. You will also need to add the following helper method (add it directly below the CreateNewOutputRows()  method).

 

  1. Here is a breakdown of exactly what we are doing:
  1. We open the file and prepare it for reading

 

  1. We continue reading until we come across an element with the name “Notice”.

 

  1. We store the entire contents of the Notice node we have found in a variable called xml

 

  1. We create a new row for output (With the AddRow() method). The important part is how we prepare the xml to become the contents (DocumentBody) of the note we will create. By converting to a Base64String we will not receive any error when we try to download the note later.

 

This concludes part one of this two-part post. On my next post, we will create the parent record for the note (annotation) and the note itself using KingswaySoft. Please let me know if you want further clarification on any of the above steps. Otherwise, I hope to see you back for part two.