XML : Inserting SSIS package variable data into sql server table by using Execute -SQL task in SSIS

objective: Consume json API from web API and shred data generically and save in sql server 2014 data base. Since we don't have native json support in 2014 and planing to use native xml support with open xml

First I am trying to convert JSON API into xml file and then assigning xml file data in to one SSIS package variable.

      public void Main()      {          // TODO: Add your code here              Object httpConn  = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);          HttpClientConnection myConnection = new HttpClientConnection(httpConn);          myConnection.ServerURL = string.Format(("http://xxxx.com/api/XX"),"XXXX","XXXX");          byte[] webdata = myConnection.DownloadData();            string result_data = Convert.ToBase64String(webdata);          XmlDocument xd = new XmlDocument();          XmlDictionaryReader xr = JsonReaderWriterFactory.CreateJsonReader(webdata, XmlDictionaryReaderQuotas.Max);          xr.Read();          xd.LoadXml(xr.ReadOuterXml());          xd.Save("C:\\Users\\XXXX\\Downloads\\Data.xml");            Dts.TaskResult = (int)ScriptResults.Success;      }    

Reads the xml file data into package variable by using following code:

      Public Sub Main()      '      ' Add your code here      '        Dim streamreader As New streamreader("C:\Users\XXXXX\Downloads\Data.xml")        Dim StreamText As String = streamreader.ReadToEnd()        Dts.Variables("Var_xmldata").Value = StreamText        Dts.TaskResult = ScriptResults.Success  End Sub    

Now SSIS package variable holds the xml file data in string format.

After doing this I am using execute sql task to load package variable data into one sql server table. In SSIS-execute sql task,creating variable with Nvarchar data and sql server table created with Nvacrhar(max).

enter image description here

When I am executing SSIS pacakge,I am getting following error-

[Execute SQL Task] Error: Executing the query " Insert into [stage].tbl values..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any help in finding whats wrong with code will be appreciable.

**Restrictions:

1. We don't have use any third party component to serialize and de serailize json or xml
2. Due to restriction #1, we need to insert the entire xml doc into a table in tsql db. Unless we can generically shred the XML in C# without 3rd party dlls, we have to follow restriction #2. **