Cheatable XML task in SSIS

来源：互联网发布：淘宝最常见韩国模特编辑：程序博客网时间：2024/05/18 20:46

You may get fooled when using variables as the XML source in XML task. It does not work at all and you probably catch the error as below

Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".
Error: 0xC002928F at XML Task, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.

Why? I am not informed.

Workarounds? Yes!

1. Create fileConnection in connnection manage, mapping the variable to this connection in expression"connection string" of the property of this object.

then use this fileconncetion in the XML task.

2. before using XML task, leverage a variable to load the XML content by coding in script task.

1) Use a Script task to set value for "v". Remember to put "User::v" at script task's "ReadWriteVariables" property. Then use the following script at Script task

Public Sub Main()
        Dim xmld As System.Xml.XmlDocument
        xmld = New System.Xml.XmlDocument
        xmld.Load("myxslt1.xml")
        Dts.Variables("v").Value = xmld.OuterXml

Dts.TaskResult = Dts.Results.Success
End Sub

2) Put the XMLTask after the Script task in the control flow and point your xslt source to "User::v" at XMLTask.

I verified the first option in my project and suggest to use this one.