I often hear comments along the lines of “Why doesn’t SSIS have an XML destination?”. Unfortunately, I don’t know the answer to that, but I can show you how to implement your own XML destination fairly easily. Thanks to the magic of the script component, it doesn’t take much work at all. This was also a good opportunity for me to use a destination script component, as I haven’t had much need for this in my work.

One caveat to this is that I have kept the XML format very simple and very generic. You can customize the script to handle more complex scenarios, but I’d imagine there would be a lot of work in producing a component that can handle all the different ways of formatting data in XML. Maybe that’s why MS didn’t include it out of the box

As an example, I have a very simple package with a single data flow. The data flow has a flat file source, and a script destination component.

The flat file source is standard, nothing interesting there. The work is all done in the script. When adding the script component, make sure to specify that it is a destination component.

When configuring the script component destination, I chose all the input columns that I wanted to include in the XML (in this case, all of the columns from my flat file).

There is no need to alter anything on the Inputs and Outputs page. Since this is a destination component, no output is necessary. On the Connection Managers page, I added a reference to a connection manager of type ”File”, and named it Destination.

This is the complete script for the component. I’ll explain it section by section below.

xmlWriter is a StreamWriter, the .NET Framework object used to create the XML file. For more information on this, please see the MSDN documentation. rootElement is the value to enclose the entire XML document with, and rowElement defines what to enclose each individual row in. By changing the values in these variables, you affect what the final XML output looks like.

targetFile holds the path and file name for the destination file. It is set in the AcquireConnections method. Interesting note: when you are working with a file connection manager, calling AcquireConnection simply returns the path and file name of the file. Why not just use a variable or set it explicitly? Doing it this way makes the script behave more like a standard SSIS destination, and makes it clearer what you’d need to change to put the file in a new destination. As a plus, if you set an expression or configuration on the ConnectionString property of the file connection manager, the script will use it automatically.

The ProcessInputRow method is where most of the work occurs. I’m using the System.Reflection and the ComponentMetaData objects to process the Row object dynamically. Essentially, this code determines what input columns are available at runtime, based on what was selected in the Script component’s property pages. It then writes each of those columns to the file, surrounding it with an XML tag based on the column name.

The nicest thing about this script, in my opinion, is that it does not have to be modified if the input columns change. The use of the System.Reflection classes allows the code to process the Row object at runtime. This does come at the expense of performance, though, so I wouldn’t recommend using this in high volume scenarios without some tweaks.

I was looking for a way to create a generic script, and figured that I could use the metadata to make it a bit more dynamic. However, I haven’t done a lot of testing on it, so I’m not sure if there are any hidden problems.

> Is the column value NULL? That might cause a problem.
I thought that was the problem too… so I filled a textfile with 10 rows with 711 times an ‘X’, but that didn’t fix the problem. I also added a Dataviewer between the source and the script destination and saw that all columns (all are dt_str) where filled.

After that I wrote a try catch around the “columnValue.GetValue(Row, Nothing).ToString()”:

The result is that some columns have the correct value and others show “Object reference not set to an instance of an object.” as value:XXXObject reference not set to an instance of an object.Object reference not set to an instance of an object.X

After that I changed the data in de textfile and saw that the same rows failed. But all rows have the same definition. The only difference is the length of the column. I’m totally flabbergasted…

I rewrote your script a little bit en used the ‘ProcessInput’ instead of ‘Input0_ProcessInputRow’ and now it works fine again. Don’t know what went wrong. Thanks for the help. Let me know if you want the source code.

I used a similar approach when I needed to output an XML file this time last year. However, I did reflection on the rowType class so I could detect and skip null values. I also used the overloaded WriteValue method so values like dates are written out in the ISO 8601 format (2000-01-01T00:00:00 instead of 1/1/2000 12:00:00 AM).

For Each prop As PropertyInfo In rowType.GetProperties()
If Not prop.Name.EndsWith(“IsNull”) Then
If CBool(rowType.GetProperty(prop.Name & _
“_IsNull”).GetValue(Row, Nothing)) = False Then
writer.WriteStartElement(prop.Name)
writer.WriteValue(prop.GetValue(Row, Nothing))
writer.WriteEndElement()
End If
End If
Next

Warning 2 ‘ScriptMain’ is not CLS-compliant because it derives from ‘UserComponent’, which is not CLS-compliant. C:\Documents and Settings\umemon01\Local Settings\Temp\SSIS\SC_dba966fa656444d3b5d08a765ea50f03\main.vb 8 14 SC_dba966fa656444d3b5d08a765ea50f03