In this blog, you will learn about how to get data from weather API and store the result as a JSON file in your system.
Steps to get API data using custom C# code in SSIS.
Prerequisites
To get data from API, first, create an integration service project in visual studio. And, you should have the SSDT tool installed into your system.
Once the SSIS project is created, click on Project. params from solution explorer. Add the following two parameters.
Parameters
- FileName – holds the file path where it stores the API data in JSON format.
- ServiceURL – API endpoint from where we get data.
From the SSIS toolbox, drag and drop the data flow task.
Double-click on the data flow task and drag & drop the script component from the SSIS toolbox. It leads you to select the component type. Select source as type and click ok.
Double-click on the script component à Under custom properties, click on the ReadOnlyVariables à Click on three dots and select the parameters ServiceURL and FileName. à Click Ok.
Now, Switch to the inputs and outputs tab and do the following.
- Rename the outputs to APIResults.
- Double-click on APIResults.
- Click on output columns.
- Add column FileData and datatype as image [DT_IMAGE].
- Add column FileName à datatype as string [DT_STR] à Length as 150.
- Click ok.
Next, click on Edit script to add the custom script to get the data from API.
One needs to add the references before adding the script. Right-click on the Reference in the solution explorer and click Add Reference. Select System.Net, System.Net.Http, and System.Net.Http.WebRequest. And, Click Ok à close the script window.
Make sure you have added these namespaces in the script.
Under public override void CreateNewOutputRows() block, Add the following script and click on save all.
Script
public override void CreateNewOutputRows()
{
ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
string url = this.Variables.ServiceURL;
string filename = this.Variables.FileName;
string useragent = “SSIS for POC”;
System.Net.HttpWebRequest req = System.Net.HttpWebRequest.CreateHttp(url);
req.UserAgent = useragent;
System.Net.HttpWebResponse resp = (System.Net.HttpWebResponse)req.GetResponse();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
resp.GetResponseStream().CopyTo(ms);
APIResultsBuffer.AddRow();
APIResultsBuffer.FileData.AddBlobData(ms.ToArray());
APIResultsBuffer.FileName = filename;
}
Once the Script is saved close the window and go to the Data flow task.
Drag and drop the extract column from the SSIS toolbox. And, connect the Script component with the extract column activity.
In the column tab, select FileData as the extract column, FileName as File Path Column, and click the force truncate checkbox. Click Ok.
Save the Package and Run the package to get the API data as a JSON file in the specified path.
Gokul
Addend Analytics