Welcome back for part 2 of this 2-part blog. In this blog, we will complete the XML node import by implementing steps 3-5 below (see part 1 for steps 1 & 2)
- Create an SSIS “For Each Loop Container” to get the file name of the large XML file for processing.
- Create 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 for an example) that will be stored against the parent records (also to be created).
- Add an SSIS Derived Column Transformation to insert some additional metadata fields to our dataflow activity.
- Create a parent record that will be associated with the xml node (CRM note) record.
- Create the note (annotation).
- Begin by adding a Derived Column Transformation to the dataflow from the SSIS toolbox.
- Drag the dataflow path arrow from the script component we created in Part 1 of the blog to the newly added Derived Column. It should look like this:
- Open the Derived Column Transformation Editor and add any additional metadata required. The metadata I have added for this example is as follows:
Here is an explanation of each element:
- Length: The expression LEN(DocumentBody) allows us to see how many characters the XML notice node contains.
- M_ImportFileName: The name of the physical file containing the XML notices.
We now have all the data we need moving through the dataflow so we can begin injecting it into CRM via the KingswaySoft SSIS components. To achieve this perform the following steps:
- Drag a Dynamics CRM Destination onto the Data Flow.
- Connect the Derived column transformation to the CRM Destination.
- Open the Destination Editor and on the General tab configure it as follows (replacing the Destination Entity with your own target entity):
- In the Columns tab, you can specify which columns are set by which corresponding dataflow fields.
Note: the fields M_DateOfFile & DefaultName were added to the data flow via the source Script Component.
Now that we have created the parent record, we can create the note (annotation) to attach to the parent record.
- Add another Dynamics CRM Destination to the dataflow and configure it as follows:
- Action: Create
- Destination Entity: annotation
- In the Columns tab, map it as follows:
Note: the fields DocumentBody, ObjectTypeCode, Subject, MimeType & FileName were added to the dataflow via the source Script Component. The field CRMRecordId was added to the dataflow by the previous CRM destination (step 6).
- The final dataflow should look something like this:
You should now be able to run the package and see your parent records created with an associated note attachment containing the XML notice.
The aim of this blog has been to demonstrate how KingswaySoft can be used to create note attachments containing large files using an illustrated example. I hope this will assist you with your own integration.