THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Outputting data to an XML file : SSIS video nugget

The last video nugget that I produced seemed to be fairly well received so I thought I would attempt a few more; and indeed, here is the next one. In this video I demonstrate a simple technique for outputting data to an XML file using SSIS’s Script Task.

The video is 8m22s long and is embedded below however I’m aware of some problems that we are having, at the time of writing, with embedding of videos here on SQLBlog so for the time being head over to where it can be viewed.

If you are so inclined I have made the package that I built whilst recording this demonstration available on my SkyDrive for your reference.

I look forward to your comments.


Published Saturday, February 27, 2010 5:53 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Alejandro Mesa said:

Thanks, Jamie.

February 28, 2010 10:54 AM

Andy said:

Are there any restrictions to the XML that can be generated directly by TSQL?

Was thinking that you could use a script destination for this instead?

March 1, 2010 9:37 AM

jamiet said:

Hi Andy,

To be honest I'm not an expert on the FOR XML clause so wouldn't want to say either way whether there are restrictions or not. The AUTO option is a default, there are other options that are much more configurable.

And yes, you could indeed use a Script component as a destination. John Welch provides the code for doing that at:


March 1, 2010 9:48 AM

David Charron said:

Thanks! Great tutorial :)

March 2, 2010 10:32 AM

Sudeep Raj said:

Hi Andy,

I have worked with pretty complex XML by using T-SQL.

I do feel that T-SQL provides quiet an extensive options for XML formation.

March 2, 2010 2:56 PM

Mike said:

Hi Jamie, Thanks awesome post, this has almost fixed a problem I've been having. The problem is it appends a <Root> element, which I'd already defined in the Sql, do you know if it's possible to amend/exclude this element?



July 8, 2010 3:50 AM

Jitain Raheja said:

Hi Jamie!

When i stumbled upon this video; in an act to anticipate how you'd handle this problem, i felt you'd use the DataFlowTask read with the OLEDB Source & Use the Script Task as a Destination.

Will try to check if that's feasible. :)

Thnx for this post!!

June 13, 2012 9:19 AM

Jonelyn said:

Is there a way where we can add a header to the xml output. I need to have <?xml version="1.0" ?> at the beginning of the xml file

October 19, 2012 2:11 AM

Zen said:

I watched the video (Thanks!) I executed the package without error. The xml file was created in C: drive. However, the file has 0 kb (empty). I don't know why.

April 20, 2015 12:29 PM

Leave a Comment


This Blog


Privacy Statement