Fetching data from WEB API using SSIS

  • Hi everyone,

    I am using SSIS script component to fetch and store data in to my sql tables from web api.

    I have designed following code which is working when I am hardcoding a json format string but its not fetching the data from web link and giving some sort of error. Web links are commented in below code:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.Web.Script.Serialization;
    using System.Collections.Generic;
    using System.Net;

    public class ScriptMain : UserComponent
    public override void CreateNewOutputRows()

    var json = @"{""data"":[{""created_at"":""2016-03-12 09:45:00"",""created_at_unix"":1457772300,""shop_name"":""DK - Some name"",""location_id"":1111,""custom_location_id"":""2222"",""custom_shop_id"":""2222"",""shop_id"":3333,""count_in"":""1"",""count_out"":""1"",""timekey"":3},{""created_at"":""2016-03-12 09:45:00"",""created_at_unix"":1457772300,""shop_name"":""test2"",""location_id"":1111,""custom_location_id"":""2222"",""custom_shop_id"":""2222"",""shop_id"":3333,""count_in"":""1"",""count_out"":""1"",""timekey"":3}]}";

    //var url = "https://nfs.punjab.gov.pk/Home/GetJosn?filter=1120189456693";
    //var json = new WebClient().DownloadString(url);

    RootObject Test = new JavaScriptSerializer().Deserialize<RootObject>(json);

    * This is where data gets added to the output buffer.
    * After AddRow() you are basically mapping the column you manually added(on the left) to the data(on the right).
    * using a foreach loop to loop through the deserialize json
    foreach (var item in Test.data)
    Output0Buffer.createdat = item.created_at;
    Output0Buffer.shopname = item.shop_name;
    public class RootObject
    public List<data> data { get; set; }

    public class data

    public string created_at { get; set; }
    public int created_at_unix { get; set; }
    public string shop_name { get; set; }
    public int location_id { get; set; }
    public string custom_location_id { get; set; }
    public string custom_shop_id { get; set; }
    public int shop_id { get; set; }
    public string count_in { get; set; }
    public string count_out { get; set; }
    public int timekey { get; set; }


    Can anyone guide me how can I fix this?



  • what errors you getting? and did you try debugging the code in case the error you get does not reflect the true error?

  • frederico_fonseca wrote:

    what errors you getting? and did you try debugging the code in case the error you get does not reflect the true error?


    Yes I have tried few things....


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

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