Hello, I have one xml file in some share network drive which I want to load. I can load the XML file into XML Source. But Can I save that XML data into some XML variable that I can pass into a stored procedure? if Yes How Can I save the XML into a XML variable in SSIS. If the above is not possible, I was thinking to pass the XML File path to the stored procedure and from with in the SP I will use "SELECT * FROM OPEROWSET (BULK..." to load the XML into variable and process. Please advice which method I should opt here if I look into performance and multiuser case? Thanks.

Since XML is a plain text file, why not your stored procedure would accept a variable of type text e.g. varchar(max)? This way you can pump the XML into a string variable in SSIS.Arthur My Blog

Thanks Arthur, I think DT_WSTR is the type is SSIS that can save XML. So I was looking for how can I save the XML into this varaible in SSIS? I have never used like this :(.

Package variable of type string would do.Arthur My Blog

I think I was not clear... Here is what I was looking for: I have a Package Variable that wil have the path of the XML file (ex: \\svr\sharea\file.xml). Now where / which component I should use to asign/load the xml file into the string variable. Sorry for the newbee question.

Is this similar to what you need: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/9c82ca81-de22-4bc6-9ba4-1a6b2d3e03bb ?Arthur My Blog

I think this will work for me. I will surely give a try and update you :). Thanks.