SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS Package for exporting Query to XML
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/25/2013 :  04:50:14  Show Profile  Reply with Quote
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

Netherlands
15 Posts

Posted - 02/25/2013 :  04:58:38  Show Profile  Reply with Quote
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
Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/25/2013 :  05:54:50  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/25/2013 :  05:57:10  Show Profile  Reply with Quote
why not use System.IO.File.AppendAllText in script task?

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

Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/25/2013 :  06:08:05  Show Profile  Reply with Quote
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?

Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 02/27/2013 :  04:06:41  Show Profile  Reply with Quote
With an ADO.NET connection it works!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000