How to download file from Web API using SSIS in Visual Studio 2019

When it is a simple API, then the easiest approach would be using curl on the command line to download the data as file and process that file.

Thank you both.  I will try both ways and see if I can get what I need.  Again many thanks!!

I am not having any luck with this.  The file from the URL needs to go to a managed instance Azure SQL server and I don't think that will support installing curl. And I don't think the client would allow that either.

I tried to get the HttpClient working in a script task and obliviously I need to brush up on my C# skills.  Nothing but error alerts.

For the HttpClient I create a data flow task and then in that I add a script component and select source as component type.  I open it and select edit and it opens a main.cs that has empty classes like public class scriptmain : userComponent, public override void PreExecute(),  public override void PostExecute(), and public override void CreateNewOutputRows(). I have tried the HttpClient example code from above in scriptmain but it just gives me errors. I know I am doing something wrong but not sure what.

Do you know of any example packages I can download that might lead me in the right direction?

Thank you.

I found my major mistake.  I was trying to use the script component instead of the script task.  I changed that and I am getting less errors.  PROGRESS!!!!

I am still working on it but thank you so much for the help so far.

I was able to build the script without errors but now I am not getting anything.  Here is what I have.

var client = new HttpClient();
client.BaseAddress = new Uri("https://website.abc.com");
byte[] byteArray = null;
client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray)); ;
client.DefaultRequestHeaders.Add("auth", "RxxxxF");
client.DefaultRequestHeaders.Add("api-key", "6xxxx8");
var result = await client.GetStringAsync("/rest/get-packages/");
var str = System.Text.Encoding.Default.GetString(byteArray);
File.AppendAllText("C:\temp\testfile.csv", str);

Select allOpen in new window

The task completes immediately so I don't think I am actually getting data from the api.  How can I test this to see if it is returning any errors?

do your test with a REST client first : https://chrome.google.com/webstore/detail/advanced-rest-client/hgmloofddffdnphfgcellkdfbfbjeloo

Once you're sure about the parameter, go code

Thank you for that link, it is awesome!  So my call to the api does work and does return data so it is in my SSIS code.  Thank you!

another useful tools : https://www.telerik.com/fiddler
you can track http(s) traffic
so on the computer running SSIS code, you should be able to see your call

Stepping through my script, it gets to this line and then dies.  No errors.  The task just finishes.

client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));

Select allOpen in new window

Any thoughts?

replace :

byte[] byteArray = null;

Select allOpen in new window

by (with the right username and password) :

byte[] byteArray = Encoding.ASCII.GetBytes("username:password");

Select allOpen in new window

or just remove both following lines :

byte[] byteArray = null;
client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));

Select allOpen in new window