Getting data from JSON files and load into DW table

Hi All,

I have JSON files download to some folder in network drive - \\comp-nt1\shared\JSON. There are 30 files in JSON folder. I have to get the data from these files and load into DW table using SSIS. I tried using script compnent, but it didn't work. I am not good in .net. Are there any workwrounds on that? Any help is appreciated.

Thanks,

Punia

  • Moved by Bob BeaucheminMVP Friday, October 18, 2013 10:48 PM Moved to a more relevent forum
October 18th, 2013 8:10pm

Please check this link.

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

Free Windows Admin Tool Kit Click here and download it now
October 20th, 2013 6:19pm

Thanks Altuko for your response!

The link above is getting the data from .txt files, But i have JSON file format. Any workaround about those?

Thanks,

Punia

October 21st, 2013 12:56pm

Hi there,

very long article on parsing json with TSQL:
https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

I havn't red all of it, but it seems helpful. 

Free Windows Admin Tool Kit Click here and download it now
October 21st, 2013 1:15pm

Thanks Zerosonic for your reply,

The article that you suggest is parsing the one record (that is hardcoded), but i have JSON file with thousands of records. Any idea for that how do i do that? What i know so far is using SSIS, i can use script task and call that file and deserailize that. But I am not .net person so no luck yet..

Thanks,

Punia

October 21st, 2013 2:00pm

Hello Punia,

Please have a look at MSDN How to: Serialize and Deserialize JSON Data

Free Windows Admin Tool Kit Click here and download it now
October 21st, 2013 2:12pm

If you are flexible with the choice of an ETL tool i would recommend Talend ETL. It's an open source software (based on java, extension of eclipse) and have a component to parse json. 
October 21st, 2013 2:35pm

I only have the option of using the SSIS as an ETL. Any work around on that?

Free Windows Admin Tool Kit Click here and download it now
October 21st, 2013 9:24pm

We have developed high quality JSON Component to extract data from JSON feed or JSON Files

SSIS Custom Adapter - Json Source JSON Source
  • Extract data from json string (stored in variable) or json file.
  • Support for JPath query to filter Json data.
  • Support for very large json files (streaming data).

Read more

June 4th, 2015 5:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics