Download XML File in DTSX Package

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:

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:

Leave a Reply

Your email address will not be published. Required fields are marked *