I had to download an XML-File from a Web-API with authentication to import in a MS SQL Database.
The main problem was the response format of the API which is controlled by the HTTP “Accept” Header.
For Example Internet Explorer receives a JSON file while Firefox gets an XML-File from the same address.
In the DTSX package I also received a JSON file but I need an XML file.
I took me a while to find the solution with a script task and VB so I’ll share it here:
...
Public Sub Main()
Dim WebConnection As New WebClient()
Dim creds As New NetworkCredential("username", "password")
Try
With WebConnection
.Credentials = creds
.Headers("Accept") = "application/xml"
.DownloadFile("https://server/api", "c:...file.xml")
End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem downloading file: ", ex.Message, "", 0)
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
...
I had to download an XML-File from a Web-API with authentication to import in a MS SQL Database.
The main problem was the response format of the API which is controlled by the HTTP “Accept” Header.
For Example Internet Explorer receives a JSON file while Firefox gets an XML-File from the same address.
In the DTSX package I also received a JSON file but I need an XML file.
I took me a while to find the solution with a script task and VB so I’ll share it here:
...
Public Sub Main()
Dim WebConnection As New WebClient()
Dim creds As New NetworkCredential("username", "password")
Try
With WebConnection
.Credentials = creds
.Headers("Accept") = "application/xml"
.DownloadFile("https://server/api", "c:...file.xml")
End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem downloading file: ", ex.Message, "", 0)
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
...