Having trouble using Script Componet as a Transform for XML block of data
Hi allI'm trying to read in a block of XML data from a data base table in VB. I'd like to read in thechunk of XML in the script componet and use XPath to breakup and send out seperate columns. I'm stuck on how to read in the block from the input buffer. I though I could do this:Public Overrides Sub input0_ProcessInputRow (ByVal Row As input0Buffer) Dim docNav As XPathDocument = New XPathDocument(Row.XMLChunk)) //where this would bring in the XML and create a XPath document objectwhere XML Chunk is the Input Column name. I know this should be easy to do I just have never had to work with XPath and crazy XML formatted messages.
July 3rd, 2009 1:37am

Could you tell us ur requirement.If you are getting the data from DB why dont you use OLEDB Source rather thatn get XML Stream and parse it in the Script component.Are you using the scrip component as the Source?Hope this helps !! - Sudeep | Please mark the post(s) as Answered that answers your query.
Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2009 12:20pm

Sure thing,No i'm using the script as a transform. Below is my requirements & discriptionI have a data base which has xml data type. I have a OLE DB Source connection brining in the xml columns. For my case its called DataItemPackage. I'm using a Script Component as at Transform. I want to bring in the xml rows and parse using XPath which I will then send the extracted data to other tables. I've been able to do this in the past connecting just to a document, but this is the first time i've tried pulling from a database of the data type xml.For my Script transformation Editor I have input buffer named Input and a column called DataItemPackage. For Output buffer Its called Output with a column name of DataItemIDPublic Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)Dim docNav As XPathDocument = New XPathDocument(Row.DataItemPackage) //This here is where I know I'm wrong, it says a mulitple viloationsDim nav As XPathNavigator = docNav.CreateNavigator()Dim NodeIter As XPathNodeIteratorDim strExpression As StringstrExpression = "//*[local-name()='DataItemID']"NodeIter = nav.Select(strExpression)NodeIter.MoveNext()OutputBuffer.AddRow() <---//This isn't right either its saying OutputBuffer is not declared, I should probably do using CreateNewOutputRows() in another methodOutputBuffer.DataItemID = NodeIter.Current.Value End Sub So is this considered a xml stream ? should I be using instead of XPathDocument XPathDataDocument?Thanks for helping.
July 3rd, 2009 8:46pm

No ideas? I've been able to pass the Row.DataItemPackage to an object such asDim doc As Object = Row.DataItemPackageHow to I get this object converted to a XML document or better yet a XPathDocument.this does not workDim docNav As XPathDocument = new XPathDocument(doc)I'm tired of going round and round on this. Someone please help!
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2009 1:04am

Hi,Cant you use a query to retrieve the XML directly from the SQL Table like:Select [XMLColumn] FROM [Table]Where ()FOR XML PATH ('Header')This should give you the XML in the form of XML.use this query in the execute SQL Task.Set the result set to a string varibale.This string variable can be used to create a XMLDocument in script task and be use using XPath the way you want it to.Hope this helps !! - Sudeep | Please mark the post(s) as Answered that answers your query.
July 6th, 2009 1:11pm

I'll give it a try. I'm about to pull my hair out on this one. I get two different errors for everything I try. Can you point me to an example if possiable?
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2009 5:26pm

I dont have a link to provide as an example.I did this stuff about a year o 2 ago.This way it should work, All the best ;)Hope this helps !! - Sudeep | Please mark the post(s) as Answered that answers your query.
July 6th, 2009 5:45pm

The XML data type is treated as NTEXT by SSIS, if I recall correctly. That means you need to use the GetBlobData methods to read it in your script component.John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2009 11:34pm

Are you talking about the VB script above like Like GetBlobData.Row.DataItemPackage or are you talking about the SQL statment method? Questions: about the SQL task am I using SQLSourceType Variable then defining a new user Variable which i will call on the script componet side using the Me.Variables.MyVariableName Like thisDim docNav As XPathDocument = New XPathDocument(Me.Variables.MyVariableName)
July 7th, 2009 1:06am

I have Dim paramBlob As Byte() paramBlob = Row.DataItemPackage.GetBlobData(0, Row.DataItemPackage.Length)Dim str As String = Convert.ToString(paramBlob)Dim docNav As XPathDocument = New XPathDocument(str) //this should work, but I get Could not find file 'C:\Documents and Settings\Ray\Desktop\Sources\2.0\instances\System.Byte[]':Damn I thought I almost had it. I know I can get this work I'm just messing one little detail. No matter what I try I get the same errorDim datadoc As XmlDataDocument = New XmlDataDocument()datadoc.Load(str) // Gives Could not find file 'C:\Documents and Settings\Ray\Desktop\Sources\2.0\instances\System.Byte[]':Is it looking for a XML header? Like this <?xml version="1.0" encoding="UTF-8"?> Its not there as it was stripped out should Iappend the tag back in?
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2009 5:44am

No, you're not converting the blob data correctly, so you are getting the type name as your string value instead of the actual XML.Try this instead: Dim paramBlob As Byte() paramBlob = Row.DataItemPackage.GetBlobData(0, Row.DataItemPackage.Length) Dim ascEncoding as UnicodeEncoding= new UnicodeEncoding(); Dim arrChars as Char() = ascEncoding.GetChars(temp);Dim str As String = new String(arrChars);John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
July 7th, 2009 4:09pm

I'll try it out. That looks like the missing puzzle piece. Thanks for the help.
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2009 5:41pm

AGHHHHHHHHHHH!!!!So now I get: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.I wasn't aware there were char limits on XPathDocument. Here is the code I have based on your suggestion Dim paramBlob As Byte() paramBlob =Row.DataItemPackage.GetBlobData(0, Row.DataItemPackage.Length)Dim ascEncoding As UnicodeEncoding = New UnicodeEncoding()Dim arrChars As Char() = ascEncoding.GetChars(paramBlob)Dim str As String = New String(arrChars)Dim docNav As XPathDocument = New XPathDocument(str)I've also tried Dim instance As XmlDocument = New XmlDocument()instance.Load(str)and get the same error.
July 8th, 2009 3:24am

I think I just about got it. Dim paramBlob As Byte()paramBlob = Row.DataItemPackage.GetBlobData(0, Row.DataItemPackage.Length)Dim ascEncoding As UnicodeEncoding = New UnicodeEncoding()Dim arrChars As Char() = ascEncoding.GetChars(paramBlob)Dim str As String = New String(arrChars)Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(str))Dim docNav As XPathDocument = New XPathDocument(ms)Now its telling me data invalid at line1, position1. So i'm farther along. I'm getting the XML out of the database converting to a string and XPath can see it. So i'm making progress.I tell you this seems to be alot hard than it should be.
Free Windows Admin Tool Kit Click here and download it now
July 8th, 2009 8:47pm

Ok, I got it. Thanks to your help Jwelch I'll list my code for others who ever have the same problems. I'm pulling LARGE chunks of XML from a SQL Server xml data type through the pipeline. I used Script Componet as a transform. I want toread in a large Chunk of XML and break up using XPathand insert into another data base. Why you might ask?Because i'm a sadist.Imports System.XmlImports System.Xml.XPathImports System.IOImports System.TextPublic Overrides Sub Input_ProcessInputRow (ByVal Row As InputBuffer) Dim paraBlob As Byte() paraBlob = Row.DataItemPackage.GetBlobData(0, Row.DataItemPackage.Length) Dim ascEncoding As UnicodeEncoding = New UnicodeEncoding() Dim arrChars As Char() = ascEncoding.GetChars(paramBlob) Dim str As String = Convert.ToString(arrChars) Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(str)) Dim docNav As XPathDocument = New XPathdocument(ms)End SubThat seems to me it should have been alot easier than it was. Thanks again to jwelch
July 8th, 2009 11:04pm

I have similar requirements. currently we are loading the data into a staging table with a xml column defined, then from T-sql we run different xqueries to get at the xml data, and move it along. I'm curious how well your script transform performs in SSIS and if its is comparable in speed to the T-SQL solution we are using. Have you done any benchmarking on your component? I would be worried about memory leaks or at least running out of virtual memory if the XpathDocument don't get disposed of correctly. Thanks for posting your code If I get sometime I will try and prototype one of our packages.
Free Windows Admin Tool Kit Click here and download it now
July 15th, 2009 8:16am

For another sample, which does extraction using XPath in the data flow check this script.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
April 14th, 2011 10:58am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics