Hi,
I have an interesting problem with a simple SSIS package that I cannot debug easily, I was wondering if I could get pointers as to how I can eiher debug it better or solve my problem.
I have a simple package that reads a table and puts the data into a staging area. Then, there is a sequence container that reads the data using an ADO connection. The resultset is mapped onto an Object variable. Inside the container, there is a ForEach container that reads the Object variable and puts it into a String variable. The ForEach container then has a data flow task with an XML task. The XML task is using an XSD schema defined in a separate file. From there, there are a number of OLEDB destination components that save the contents of the XML message into normalized tables.
From a high-level perspective, I just want to read a bunch of XML strings from a table and for each, interpret it and save it in a database table based on its type. My XSD contains all possible elements so that one XML task can conditionnally send to the proper destination based on the structure read.
This works usually very well. But for some reason, once in a while, it just hangs. In debug mode, the hanging occurs when going into the data flow component after processing the first message. I have a breakpoint on my data flow that hits but when I click continue, it hangs. I checked my string variable, it does have the proper message...
The only way to stop this is for me to go and kill the SQL process attached to this whole execution. When I do, my package fails and the error handler kicks in, with an error description that says "There were errors during task validation."
Does anybody have any idea what could cause this? I'm not sure as to why this would happen, my XML is well formed etc. Could this be related to my ForEach loop...? The strangest thing is that after changing a couple of settings and putting them back the way they were, it works again for a while and then later stops
.
I'm a bit clueless so any help is greatly appreciated.
Thanks,
Greg
I have an interesting problem with a simple SSIS package that I cannot debug easily, I was wondering if I could get pointers as to how I can eiher debug it better or solve my problem.
I have a simple package that reads a table and puts the data into a staging area. Then, there is a sequence container that reads the data using an ADO connection. The resultset is mapped onto an Object variable. Inside the container, there is a ForEach container that reads the Object variable and puts it into a String variable. The ForEach container then has a data flow task with an XML task. The XML task is using an XSD schema defined in a separate file. From there, there are a number of OLEDB destination components that save the contents of the XML message into normalized tables.
From a high-level perspective, I just want to read a bunch of XML strings from a table and for each, interpret it and save it in a database table based on its type. My XSD contains all possible elements so that one XML task can conditionnally send to the proper destination based on the structure read.
This works usually very well. But for some reason, once in a while, it just hangs. In debug mode, the hanging occurs when going into the data flow component after processing the first message. I have a breakpoint on my data flow that hits but when I click continue, it hangs. I checked my string variable, it does have the proper message...
The only way to stop this is for me to go and kill the SQL process attached to this whole execution. When I do, my package fails and the error handler kicks in, with an error description that says "There were errors during task validation."
Does anybody have any idea what could cause this? I'm not sure as to why this would happen, my XML is well formed etc. Could this be related to my ForEach loop...? The strangest thing is that after changing a couple of settings and putting them back the way they were, it works again for a while and then later stops
.I'm a bit clueless so any help is greatly appreciated.
Thanks,
Greg