Extract the data from a table and create Output table value as XML using SSIS

In the present example I am using AdventureWorks2012 database. You could use any other database. I am writing a sql script that selects a number of columns from a table in the database using the For XML clause. The Select query would return an XML fragment from the database.

SELECT [BusinessEntityID]       ,[PersonType]       ,[NameStyle]       ,[Title]       ,[FirstName]       ,[MiddleName]       ,[LastName]       ,[Suffix]       ,[EmailPromotion]       ,[AdditionalContactInfo]       ,[Demographics]   FROM [Person].[Person]   FOR XML AUTO,ELEMENTS,ROOT('row')

See the output

We need to create an xml file which stores this xml result.

Open SSDT

Take Execute sql task

Double click on this and set the configuration.

Select Result set as XML

  

And create connection.

Select the source type as direct input and click on the SQL statement.

Click ok.

Create a variable

  

Now go to the Result set and map the variable name.

Click ok.

Now we need to take the Sql script task.

  

Double click on the Script task

Select the variable and click edit script button.

Writing the blow script

public void Main()                                 {             //User::ResultSetXML                                                 // TODO: Add your code here             string fileName = @"F:\TestXMLFile.xml";             try             {                 // Check if file already exists. If yes, delete it.                 if (File.Exists(fileName))                 {                     File.Delete(fileName);                 }                 // Create a new file                 using (FileStream fs = File.Create(Dts.Variables["User::ResultSetXML"].Value.ToString()))                 {                     // Add some text to file                     Byte[] title = new UTF8Encoding(true).GetBytes("New XML File");                     fs.Write(title, 0, title.Length);                     byte[] author = new UTF8Encoding(true).GetBytes("Bagesh Kumar Singh");                     fs.Write(author, 0, author.Length);                 }             }             catch (Exception Ex)             {                 Console.WriteLine(Ex.ToString());             }                                                  Dts.TaskResult = (int)ScriptResults.Success;                                 }

Save and execute the package.

Package executed successfully.

See the output.

Opening this file.

 

Got the expected output.