July 15, 2010 at 11:37 am
I have an old SSIS problem
Exporting data from SQL server 2005 using FOR XML in my select statement.
Then saving the output into a SSIS string variable.
Then using a script to write the XML string to a text (XML) file.
So I followed this great thread
http://www.sqlservercentral.com/Forums/Topic291798-148-1.aspx
Both the Execute SQL task and my script task perform just fine.
But the output is junk.
- <ROOT>
<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>
</ROOT>
I believe I have my Exec SQL result set correct. It's set to XML with the result set name = 0
Are there other settings i may be missing?
Many thanks in advance
jhh
July 15, 2010 at 1:06 pm
Here's how I've done it.
1. Create a Data Flow task.
2. Inside Data flow, use OLE DB Source adapter to get your XML. I use a stored procedure. I use the SP to build out the XML and return the XML as varchar(max). varchar(max) maps to the DT_TEXT datatype inside your data flow.
3. Route your data flow into a script task for the destination. You could route your XML directly to a flat file destination, but I use the script task to name the target file and add a standard XML header. Here's my code:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Private TargetFile As String
Private outstream As StreamWriter
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim s As String
Dim byteBLOBData As Byte()
TargetFile = Row.AMJFolder & Replace(Row.QueryName, " ", "_") & ".amj"
outstream = New StreamWriter(TargetFile, False)
outstream.WriteLine("<?xml version=""1.0"" encoding=""UTF-8"" standalone=""no""?>")
outstream.WriteLine("<?OLE_DB_AXSMOD_FirstCompatibleVersion 12.0?>")
outstream.WriteLine("<!--Configuration information for the OLE DB AXSMOD-->")
byteBLOBData = Row.AMJInfo.GetBlobData(0, Row.AMJInfo.Length)
s = System.Text.Encoding.UTF8.GetString(byteBLOBData)
outstream.WriteLine(s)
outstream.Close()
End Sub
End Class
You'll notice that I name the file with a .amj extension. If you change this to a .xml extension, it will create the XML file with all of the spacing/formatting from the FOR XML output that you used to create the varchar(max) variable that was routed to the DT_TEXT data type and into the AMJInfo value in the data flow.
July 15, 2010 at 1:10 pm
Thanks John,
I'll give it a try
jhh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply