how to load DAT File which contains JSON object as one of the field into a Table

Question

Post reply

how to load DAT File which contains JSON object as one of the field into a Table

saidwarak01

SSCommitted

Points: 1577
More actions
July 20, 2010 at 2:08 pm

#141325

Hi Everyone,
I have a dat file with 20 columns and among these 20 columns I have one column which is a JSON object and inside this JSON object their are many value pairs. I have to load these value pairs inside the JSON and other column values from the DAT file into a table. I think it is better to provide example on the problem.
I do have a table called ERROR_LOG and it contains following fields
Create table ERROR_LOG (
ErrorCategory varchar(255)
,Severity int
,SourceSystem varchar(255)
,cal_year varchar(10) --comes from etldescription JSON object
,cal_month smallint --comes from etldescription JSON object
,org_year int --comes from etldescription JSON object
,batch_id varchar(255) --comes from etldescription JSON object
,EtlBatchSid varchar(255))
Now the data inside the DAT file is as follows:
"ErrorCategory","Severity","SourceSystem","Etldescription","EtlBatchsid"
"INFORMATION",2,"CFM","DoeInfo: Successful: {'cal_year': 2010, 'cal_month': 7, 'org_year': '2010', 'batch_id': '20100708151438'}","20100708151438"
"INFORMATION",2,"CFM","DoeInfo: Successful: {'cal_year': 2010, 'cal_month': 7, 'org_year': '2010', 'batch_id': '20100708151438'}","20100708151438"
"INFORMATION",2,"CFM","DoeInfo: Successful: {'cal_year': 2010, 'cal_month': 7, 'org_year': '2010', 'batch_id': '20100708151438'}","20100708151438"
this is only sample and actual JSON object is ETLdescription field inside the DAT file and I want to extract cal_year, cal_month,org_year,batch_id into seperate columns.
Please let me know if anyone has any idea to resove this problem using script task or any other way in SSIS.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

saidwarak01 SSCommitted Points: 1577 More actions · Answer 1

Hi Everyone,

Can anyone provide me any solution or idea on this problem?

Thanks in advance

saidwarak01 SSCommitted Points: 1577 More actions · Answer 2

Moderators,

Can you please throw somelight on this problem.

It is very urgent task and I need help from you guys.

Thanks

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

saidwarak01 (7/21/2010)
Moderators,
Can you please throw somelight on this problem.
It is very urgent task and I need help from you guys.
Thanks

Hey,

People are on vacation... come back in September 😉

Just silly joke.. don't get mad . I understand your frustration. You have to roll your sleeves and write some code. You will also need third-party library to process the JSON structure. I have used LitJson[/url] with good results, but there are others .NET projects around.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

saidwarak01 SSCommitted Points: 1577 More actions · Answer 4

Hi,

first of all thanks for your reply. It would be appreciable if you can share more information on my problem.

Thanks.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

saidwarak01 (7/22/2010)
Hi,
first of all thanks for your reply. It would be appreciable if you can share more information on my problem.
Thanks.

I have shared everything you need to start working on the task. I will not be able to provide code because I'm busy.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/