Link to home
Start Free TrialLog in
Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on

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

I am trying to use Visual Studio 2019 Integration Services project to get a json response from a web api.  So far I am very confused.


Here is what I have.

URL = https://websitename.abc.com/rest/get-packages
auth: RxxxxxxxxF
api-key: 6xxxxxxxd8

Open in new window

I am following this example on how to do this with a HTTP data source and script task.

http://microsoft-ssis.blogspot.com/2011/05/download-source-file-from-website-with.html

But not having much luck.  I have not written C# in years and never really got into it deeply.  I can get the data with Power Bi and when I look at the source for that this is what I see.

Source = Json.Document(Web.Contents("https://websitename.abc.com/rest/get-packages", [Headers=[auth="RxxxxxxxxF", #"api-key"="6xxxxxxxd8"]]))

Open in new window

Can anyone point me in the right direction on how to do this?  I can't afford a $1500 custom component from some of the third party guys.

Thank you!!!



Avatar of ste5an
ste5an
Flag of Germany image

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.
ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Youmans

ASKER

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);

Open 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));

Open in new window

Any thoughts?
replace :
byte[] byteArray = null;

Open in new window

by (with the right username and password) :
byte[] byteArray = Encoding.ASCII.GetBytes("username:password");

Open 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));

Open in new window