March 17, 2023 at 10:04 am
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;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
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.AddRow();
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?
Regards
March 17, 2023 at 11:22 am
what errors you getting? and did you try debugging the code in case the error you get does not reflect the true error?
March 17, 2023 at 1:58 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy