| Author |
 Topic  |
|
|
MCPietersTP
Starting Member
Netherlands
15 Posts |
 Posted - 02/25/2013 : 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
Netherlands
15 Posts |
Posted - 02/25/2013 : 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
Netherlands
15 Posts |
Posted - 02/25/2013 : 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
India
48076 Posts |
Posted - 02/25/2013 : 05:57:10
|
why not use System.IO.File.AppendAllText in script task?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
MCPietersTP
Starting Member
Netherlands
15 Posts |
Posted - 02/25/2013 : 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
Netherlands
15 Posts |
Posted - 02/27/2013 : 04:06:41
|
| With an ADO.NET connection it works!!! |
|
|
| |
Topic |
|
|
|
SSIS Package for exporting Query to XML
