Reading JSON file and loading into in the table using SSIS Using Script Task

 If we want to validate any data from the JSON file like Email id validation. In this case we will read the JSON file parse the data and do the validation then load it into the database.

Below is the JSON file.   

This file is having 4 Elements.

Below is the table.

CREATE TABLE Posts_details(                 userID int NULL,                 Id int NULL,                 Post_title varchar(100) NULL,                 Post_body varchar(500) NULL )

In this post we will see how we load the JSON file using Script Task.

Taking the script task. 

We are writing the below code

public class lPosts         {               public List<posts> data { get; set; }         }           public class posts         {             public string userid { get; set; }             public string id { get; set; }             public string Post_title { get; set; }             public string Post_body { get; set; }         }             public void Main()         {                   try             {                 string connetionString = null;                 string sql = null;                 var serializer = new JavaScriptSerializer();                               connetionString = "Data Source=BAGESH\\BAGESHDB;Initial Catalog=TestDB; Trusted_Connection=True;";                   string jsonFilePath = @"H:\SSIS1\Source\Posts.json";                   string json = File.ReadAllText(jsonFilePath);                   var dt = serializer.Deserialize<Dictionary<string,string>[]>(json);                       using (SqlConnection cnn = new SqlConnection(connetionString))                     {                           cnn.Open();                           sql = "insert into Posts_details (userID, Id,Post_title,Post_body) values(@userID, @Id,@Post_title,@Post_body)";                           foreach (var item in dt)                         {                                                         using (SqlCommand cmd = new SqlCommand(sql, cnn))                             {                                                                cmd.Parameters.Add("@userID", SqlDbType.NVarChar).Value = item["userId"].ToString();                                 cmd.Parameters.Add("@Id", SqlDbType.NVarChar).Value = item["id"].ToString();                                 cmd.Parameters.Add("@Post_title", SqlDbType.NVarChar).Value = item["title"].ToString();                                 cmd.Parameters.Add("@Post_body", SqlDbType.NVarChar).Value = item["body"].ToString();                                                               int rowsAdded = cmd.ExecuteNonQuery();                               }                         }                         cnn.Close();                       }                       Dts.TaskResult = (int)ScriptResults.Success;                           }             catch (Exception ex)             {                 Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                 Dts.TaskResult = (int)ScriptResults.Failure;             }         }

Now package is ready to run.

See the records in the table.  

Now running this package.   

See the records in the table.

  

Record inserted successfully.