Tuesday, July 15, 2008

BI Thoughts and Theories : XML Transformations Part 2

After a bit of frustrations, I got this to work with a SqlClient rather than an OLE DB client by parsing out the connection string and doing a string replace to remove the provider and other invalid information.

I'm using something similar to create a custom error handler that exports to an XML column.  No more error tables for each dimension table.

The script is very similar to the one from the previous article, though it is a bit simpler. The XML values are prepared by iterating through the Input Collection metadata, and using the Reflection classes to read the Row object dynamically. The resulting value is put into the XML column on the output.

The OLEDB Destination simply maps the XML column to an XML column on a database table.

This is a good alternate approach to the previous one, particularly if you need to send the XML to a database instead of a flat file.

BI Thoughts and Theories : XML Transformations Part 2

No comments: