SSIS Package for exporting Query to XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

SSIS Package for exporting Query to XML

Author

Topic

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-25 : 04:50:14

Hi Everyone,

I want to make a package which exexcutes a stored procedure that has as a result XML data, that than is exported to a xml file.
I tried following instruction but I got stuck with that last part that is in VB code, and I am using C#:
In SQL Server:
1. Create a proc called “spGenerateXML” that contains your SELECT FOR XML statement
In SSIS:
1. Create a Package level string variable called "XMLVariable"
2. In Connection Manager, create a New OLEDB Connection called "SQLDB"
3. In Connection Manager, create a New File Connection called "XMLFile"
4. On Control Flow tab
a) Add Execute SQL Task
b) Add Script Task
c) Connect Execute SQL Task (parent) to Script Task (child)
5. Edit Execute SQL Task, General section
a) Change Result Set to "XML"
b) Set Connection to "SQLDB"
c) Set SQLStatement to "EXEC spGenerateXML"
6. Edit Execute SQL Task, Result Set section
a) Click Add button
b) Set Result Name to 0
c) Set Variable Name to "User::XMLVariable"
7. Edit Script Task, Script Section
a) Set ReadOnlyVariables to "XMLVariable"
b) Click Design Script button
c) Replace "Main" Subroutine with:
Public Sub Main()
Dim XMLString As String = Nothing
Dim fs As StreamWriter = Nothing
Dim strfilename As String = _
DirectCast(Dts.Connections("XMLFile").AcquireConnection(Dts.Transaction),_
String)
XMLString = Dts.Variables("XMLVariable").Value.ToString.Replace("<ROOT>",_
"").Replace("</ROOT>", "")
'XMLString = Dts.Variables("XMLVariable").Value.ToString
fs = New StreamWriter(strfilename, False)
fs.Write(XMLString)
fs.Close()
Dts.TaskResult = Dts.Results.Success
End Sub

Is it possible to translate this last code to C#, and can someone help me with this?

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-25 : 04:58:38

My stored procedure looks like this:
as
declare @XmlOutput xml
set @XmlOutput = (Select * from openquery(QODBC2, 'select top 10 ListID, TimeCreated, TimeModified, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor') as Supplierdata
for xml auto, type, elements, root('Supplierdata'))

Select @XmlOutput

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-25 : 05:54:50

I translated the VB code to c# as follows:
String XMLString = null;
String strfilename = Dts.Connections["XMLFile"].AcquireConnection(Dts.Transaction) as String;
XMLString = Dts.Variables["XMLVariable"].Value as String;
System.IO.StreamWriter fs = new System.IO.StreamWriter(strfilename, false);
fs.Write(XMLString);
fs.Close();
Dts.TaskResult = (int)ScriptResults.Success;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 05:57:10

why not use System.IO.File.AppendAllText in script task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-25 : 06:08:05

But I get this as output:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>

When I exec stored procedure within sql server I get no error but XML data...

Can someone help me with this error?

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-27 : 04:06:41

With an ADO.NET connection it works!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources