Exporting XML columns from SQL to file in SSIS

  • [アーティクル]

I had a requirement to facilitate some testing with an internal customer which involves periodically exporting XML which we store in SQL Server as XML data types.  One requirement was to use some column data (e.g. a sequence number, dateTime and identifier) being returned in the query as part of the file name.  Obviously I was going to do this in SSIS, but wasn't sure how I was going to do this as there is no XML destination type (like there is for FlatFile), nor have I tried to use per-row information as part of the file name.

The solution was actually ridiculously simple via the expedient of using a custom script.  In my control flow, I had a file system task to create the output folder if necessary, and a Data Flow Task.  In my Data Flow Task, I created an OLE DB Data source to run my custom SQL statement (loaded with parameters I could inject from the caller, such as date from/to parameters), and a custom script component set as a Transformation script. 

In the script component, I inject the output file path variable, and then it's a simple matter of the following:

 public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
 string fileName = string.Format("{0}_{1}_{2}.xml",Row.ChannelSequenceNumber,Row.ChannelDesignator,Row.Id);
 
 using (System.IO.FileStream fs = new System.IO.FileStream(string.Format(@"{0}\{1}",this.Variables.filePath,fileName),System.IO.FileMode.Create))
 using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs))
 {
 sw.Write(System.Text.Encoding.Unicode.GetString(Row.Body.GetBlobData(0, (int)Row.Body.Length)));
 sw.Close();
 }
 }

5 lines of code was all it took - the file name is created using data passed in from the SQL query and it spits out a new file for each XML blob returned in the result set.

Comments

  • Anonymous
    September 29, 2013
    The comment has been removed