I'm trying to generate an XML file through SSIS and i'm following the below mentioned steps to generate it:
In SQL Server:
1. I created a proc called “spGenerateXML” that contains my SELECT FOR XML statement.
1. I created a Package level string variable called "XMLVariable"
2. In Connection Manager, I created a New .Net Provider/SQL Client Data Provider Connection called "ADODB"
3. In Connection Manager, I created a New File Connection called "XMLFile"
4. On Control Flow tab
a) Added Execute SQL Task
b) Added Script Task
c) Connected Execute SQL Task (parent) to Script Task (child)
5. Edited Execute SQL Task, General section
a) Changed Result Set to "XML"
b) Set Connection to "ADODB"
c) Set SQLStatement to "EXEC spGenerateXML"
6. Edited Execute SQL Task, Result Set section
a) Clicked Add button
b) Set Result Name to 0
c) Set Variable Name to "User::XMLVariable"
7. Edited Script Task, Script Section
a) Set ReadOnlyVariables to "XMLVariable"
b) Click Design Script button
c) Replaced "Main" Subroutine with:
View Complete Post