Post reply

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

  • 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.

  • July 21, 2010 at 8:46 am

    #1196206

    Hi Everyone,

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

    Thanks in advance

  • July 21, 2010 at 10:31 am

    #1196290

    Moderators,

    Can you please throw somelight on this problem.

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

    Thanks

  • July 21, 2010 at 8:38 pm

    #1196607

    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/

  • July 22, 2010 at 8:19 am

    #1196879

    Hi,

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

    Thanks.

  • July 22, 2010 at 8:23 am

    #1196883

    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/

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

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