Clash Royale CLAN TAG#URR8PPP

To read Xml file from variable in XML Source in SSIS

I am getting the result in variable through web Service Task as shown in below image,

Then I have used XML task to remove the excess namespace and stored the processed result in another variable in strProcessedResult Variable as shown in below image,

here Second operand is my data.xslt file which helps to remove extra namespace from my XML file,

And below is the whole structure of my control flow,

In Data flow , I am using XML source to read my Variable and to enter the Data into database, below is the screenshot of XML Source,

,

Here, it's giving me error as XML source was unable to read the data and XMLDataVariable was empty, I want to read the node value and insert the data into database using OLEDB Destination,

In Case if instead of saving the result from web service task into variable, I save that in XML file externally then below will be my XML File format,

<?xml version="1.0" encoding="utf-16"?>
<Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name xmlns="http://localhost">Rahul</Name>
<BirthDate xmlns="http://localhost">1991-04-20T00:00:00</BirthDate>
</Data>.


And after applying XSLT rule to remove excess namespace, my XML file will look like this,

<?xml version="1.0" encoding="utf-8"?><Data><Name>Rahul</Name><BirthDate>1991-04-20T00:00:00</BirthDate></Data>


And please make note, I have to compulsorily add the XSLT rule to remove the extra namespace otherwise it does not create the XSD for further operation and also my project works well If I store result in XML file externally but I want to make use of variable for whole operation,

1 Answer
1

Make sure you actually have something in your strProcessedResult variable at design time. By that I mean: when you run your package it will load the variable but at design time your variable will be empty, so paste in your expected XML, just to give the task something to process.

strProcessedResult

@ElectricLlama..My variable strprocessedResult do have value , the entire XML file content, I have checked it using breakpoint,I know this is not right approach then to for working process, I first operated web service task and XML task to get value in above variable and then I again collectively operated all the tools, this means my above variable already contained the value.
– Reshma
Sep 30 '13 at 5:38

So when you go and inspect that variable in the variables window (not while the code is running), it contains something?
– Nick.McDermaid
Sep 30 '13 at 23:53

@ElectricLlama.. sorry but how I am suppose to check for the content of the variable when code is not running means when it is not in debug mode?
– Reshma
Oct 1 '13 at 5:21

I only have SQL 2008 SSIS installed (not your version). In here you press SSIS / Variables. How did you create your variable in the first place? Here is a large link on variables - see if you can open the variables window and check the value: technet.microsoft.com/en-us/library/ms141085.aspx
– Nick.McDermaid
Oct 1 '13 at 8:40

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.