I'm having a problem using the Foreach Loop container that contains an XML
Task followed by a data flow task.
When I run my package in the debugger, the XML Task fails with the following
errors:

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

The Foreach Loop container is a file enumerator. The XML Task validates XML
files using an XSD file. The XML Task SourceType is a variable and the
Source has the variable set by the Foreach Loop (index 0). I have an XML
file which I know is valid.
For some reason, the XML Task Source is not referencing the variable, set by
the Foreach loop. If I use a file connection to the existing XML file,
instead of using the variable, the XML Task validates the file successfully.

I believe there might be a bug in the XML Task when a variable is specified.
If I disable the task and let my data flow task run, it has no problem
importing each XML file, in the directory, using an XML Source that uses the
Foreach loop variable for the Source. My data flow completes correctly.

Am I doing something wrong here or does this sound like a bug? Any
workarounds?

Hello,

This is a limitation of SSIS. Some other tasks return an Xml DOM document
or other format XML stored in variable. Now the result can't be immediately
consumed by the Xml task because it takes Xml string only. Users would have
to add a script task (or by some other means) to translate the DOM
document/Other format to an xml string.

This limiation is noticed by product team and they have started to evaluate
this issue to see if there shall be design change on this feature. I also
encourage you submit your feedback on this feature via the link below so
that product team could hear more sounds on this from users.

http://lab.msdn.microsoft.com/produc...k/default.aspx

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.

Peter,

I'm not sure I understand what this has to do with the variable set by
Foreach. It was my understanding that Foreach loop assigns the path and
filename, not the actual content of the file, in the variable. I wanted this
fully qualified path to be used as the XML Task Source.

I'm looking at the XML Task documentation and it says the following about
the source:
"If the source is a variable, the specified variable contains the path of
the XML document. "

If what you are saying is true, this is documented wrong.

""privatenews"" wrote:

Hello Hasan,

I am having the exact same problem as you! Must be a bug! I really need
to get this to work in my project right now, so I hope there is a fix
for it. Otherwise I need to code my own XML Task, but I don't know how
to to that, so I hope someone has a solution for this!

Regards
Maggie

Hasan Quadri skrev:

Hello again!

I was actually able to find a workaround for this!

Create a new file connection (by right clicking connection managers and
choose new File Connection), set the source of this one to an arbitrary
file first. Then, on the "Expressions" property, choose
"ConnectionString" and set this to the variable you want to use.

Set the source of your XML Task to the file connection you created. And
voila, it should work!

- maggie

Hasan Quadri skrev:

That worked, thanks for the info.

"maggie" wrote:

Hello,

I want to thanks Maggie for sharing this workaround! I tested on my side
and it works fine.

The document on XML Task is not correct and I'v reported this bug to the
proper channel. As I mentioned, there shall be design change on this
feature to meet customer's requirements better.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

Using this workaround, I'm having a problem when trying to a validate a
mixture of invalid and valid XML files. In my test, the Foreach loop
enumerates through two invalid XML files and then one valid XML file. The
XML Task generated warnings for the invalid XML files AND also for the valid
XML file. If I only have the valid XML file, the task validates it
successfully.

Is it possible that the File Connection is not getting the expression update
and always using the first enumerated file? Perhaps the task is not
resetting the state on the next iteration? Can anyone else confirm this
problem?

""privatenews"" wrote:

Hello,

Based on my test, I reproduced the issue on your side. I used a script task
to pop up a message box for the variable hosting the filepath/name. Each
time the first filename is the xml file that is not valid, and the result
is false. Even if all other files are valid, the result is still false.

I think though the connection string of file manager is modified, the
stream related to file manager in xml task is not freshed with the new file
name.

You may consider a script task to read content of xml file into variables
directly to test. You may want to see following links for related
information:

http://www.developerdotstar.com/community/node/313

http://sqljunkies.com/WebLog/knight_.../16/16088.aspx

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.

Hello,

Based on my further research, I found this issue still occurs even if I use
a script task to read xml file directly into the variable. The Foreach loop
enumerates through 1 invalid XML files and then 2 valid XML file. The XML
Task generated warnings for the invalid XML files AND also for the valid
XML file. If I only have the valid XML file, the task validates it
successfully. Following is part of code in the script task


Public Sub Main()
'
' Add your code here
Dim contents As String

Dim filepath As String = CType(Dts.Variables("Variable").Value,
String)
filepath = replace(filepath)

contents = ReadVariableContents(filepath)


Dts.Variables("Variable2").Value = contents


Dts.TaskResult = Dts.Results.Success
End Sub

Public Function ReadVariableContents(ByVal filePath As String) As String

Dim contents As String

Dim objReader As IO.StreamReader

Try

objReader = New IO.StreamReader(filePath)

contents = objReader.ReadToEnd

objReader.Close()

Catch Ex As Exception

MsgBox(Ex.Message)

End Try
Return contents

End Function

I suspect this might be a issue between xmltask and foreach container. Will
you please provide me a valid email so that I could send you the project to
test on your side? Also, I may collect more information on environment from
you so that we may further troubleshoot the issue. You could send a email
to at petery (AT) microsoft (DOT) com. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.