If you’ve ever tried to write data to an XML file with SSIS you know there’s not a slick, easy way to do it. You could use a Script Component in a Data Flow Task, as illustrated by fellow Pragmatic Works employee, Jason Strate. But there is an easier way. In comes Task Factory with its new XML Generator Transform Data Flow component.
The Task Factory XML Transform component allows you to specify the XSD file, which contains the schema information for the XML data to be output. Once you’ve selected the XSD file, any column from the source data that should be included in the XML document needs to be mapped to a matching field from the XSD file.
Next, you need to specify the relationship between the parent and child elements. Then map the field to establish the relationship.
Send the data to a Flat File Destination pointed at an XML file and you’re in business. Download the free trial of Task Factory to check out the XML Generator Transform and the 36 other tasks and transforms.