Downloading multiple json using Integration Service (SSIS)

Question

Downloading multiple json using Integration Service (SSIS)

danielmarcos

Old Hand

Points: 331
More actions
November 8, 2021 at 8:36 pm

#3949211

I need to do ETL from an API where there are 632 json files.
That is, the API starts on page 1 and goes to the last page (632).
I know the C# code to download just one file, but how to download these 632 json files using the integration service?
Can anybody help me ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Sounds like you need to add a loop to your C# code. Which part of the process are you having trouble with?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

danielmarcos Old Hand Points: 331 More actions · Answer 2

Hi Phil,

follows the code I'm using to download a file. I'm not a C# programmer.

I have trouble making this loop.

Can I loop this same code? Can you help me ?

public override void CreateNewOutputRows()
    {
        using (var client = new WebClient() { Encoding = Encoding.UTF8 })
        {
            string urlAPI = "https://url-api?key=1234&page=1";
            var pathWindows = @"C:\name-project\fileJson.json";
            client.Headers.Add("User-Agent", "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12");
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Ssl3;
            ServicePointManager.ServerCertificateValidationCallback += (sender, certificate, chain, sslPolicyErrors) => true;
            string jsonFile = client.DownloadString(urlAPI);
            System.IO.File.WriteAllText(pathWindows, jsonFile);
        }
    }

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Sure. Something like this (untested) should do it:

            for (int i = 1; i <= 632; i++)
            {
                string urlAPI = string.Concat("https://url-api?key=1234&page=",i);
                var pathWindows = string.Concat(@"C:\name-project\fileJson", i, ".json");
                client.Headers.Add("User-Agent", "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12");
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Ssl3;
                ServicePointManager.ServerCertificateValidationCallback += (sender, certificate, chain, sslPolicyErrors) => true;
                string jsonFile = client.DownloadString(urlAPI);
                System.IO.File.WriteAllText(pathWindows, jsonFile);
            }

This code would replace what is currently in your Using section (from string urlAPI to jsonFile);.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin