Currently there are lots of JSON format stored data. It’s important to be able to read the JSON data stored in files, load the data into SQL Server, and analyze it. This blog describes how to import JSON files into SQL Server via SSIS Script Component as Transformation.
I have JSON format file looks like below
{“id”:”037-002″,”accountAlias”:”Primary”,”homeCurrency”:”USD”,”marginUsed”:”0.0548″}
{“id”:”055-001″,”accountAlias”:”Primary”,”homeCurrency”:”USD”,”marginUsed”:”10.000″}
need import this format json file to database table, table schema looks like below
CREATE TABLE import_raw_accounts(
id varchar(60) NULL,
account_alias varchar(60) NULL,
home_currency varchar(60) NULL,
division varchar(3) NULL,
userid varchar(60) NULL,
marginUsed varchar(60) NULL
)
Create Script Component as Source
Step 1 : Drag and drop Script Component in the SSIS toolbox to the data flow region. Once you drop the Script component, a new pop up window called Select Script Content Type opened. Then selecting the Source option
Step 2: Within the Input and Outputs tab, Go to Output Columns to add output columns.
Step 3 : Within the Script tab, please click on the Edit Script.. button to write the actual C# Script
Once you click on the Edit Script, it will open the main.cs class file to write the C# code. Please write your custom code inside the CreateNewOutputRows function
Step 4 : Before start extract Json data, we need to create a new class for imported JSON object, which will be the same JSON data structure.
public class Accounts
{
public string Id { get; set; }
public string accountAlias { get; set; }
public string homeCurrency { get; set; }
public string division { get; set; }
public string userid { get; set; }
public string marginUsed { get; set; }
}
Step 5: Add JSON deserialize code to method CreateNewOutputRows
using (StreamReader r = new StreamReader(Variables.JsonFileName))
{
while (!r.EndOfStream)
{
string json = r.ReadLine();
var item = JsonConvert.DeserializeObject(json);
if (item != null)
{
OutputAccountsBuffer.AddRow();
OutputAccountsBuffer.id = item.Id;
OutputAccountsBuffer.accountalias = item.accountAlias;
OutputAccountsBuffer.homecurrency = item.homeCurrency;
OutputAccountsBuffer.marginUsed = item.marginUsed;
var acc = item.Id.Split(‘-‘);
if (acc.Length >= 1)
{
OutputAccountsBuffer.division = acc[1];
OutputAccountsBuffer.userid = acc[2];
}
}
}
}
Final Step: add destination source and mapping output data to table import_raw_accounts.
