How to download XML file from URL using C# or VB.Net Script Task in SSIS

Question

I am trying to download an XMLFile output from the SSRS Report. I am having issues in Script Task of C# and VB.Net

I am getting below issue

Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Dim myURL As String
myURL = "https://exmaple.com/XMLFileForDownLoad"

Dim WinHttpReq As Object


WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open("GET", myURL, False)
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
Dim oStream As Object = Nothing
If WinHttpReq.Status = 200 Then
    oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1

    oStream.Write(WinHttpReq.ResponseBody)
    oStream.SaveToFile("C:\temp\file.xml")
    oStream.Close
End If

I want to download file in the Local

It appears that you cut off the beginning of the Error Message. Was there an error code (there usually is)? — digital.aaron
Error is complete. It is inside the window DTS Script task: run Time Error Exception has been thrown by the target of an invocation. — jaiswalm
This exception is thrown when there is an error while executing the script. You must add breakpoints and debug the script code the specify the error source. — Hadi

jaiswalm jaiswalm · Accepted Answer · 2019-02-08T22:20:58

I was able to solve using VBScript following below and it worked as desired.

Execute and Download SSRS Report as XML using SSIS

How to download XML file from URL using C# or VB.Net Script Task in SSIS

1 Answers