This project has moved. For the latest updates, please go here.

Processing large JSON files

Topics: Bug / Problems, Example / Documentation
Jul 20, 2016 at 4:02 PM
I have a JSON data file that has the format:

{"grades": [{"name1":"value1,"name2":"value2"},{"name1":"value1,"name2":"value2"},{...}],
"footer":{"name3":"value3"},{"name4":"value4"}}

I first decode it as an using RawJson - then the RAW_GRADES/RAW_FOOTER as a JSON Filter.

In the JSON Source Component - I cannot specify MaxLength for each of the "grades" and "footer" to be more than 4000. This limits the size of file I can process.

My question is: how do I process large files if the maximum "OutputColumnName" (for the RAW_GRADES object) is restricted to 4000 characters? If I set it to 4001 and click OK - I get a Visual Studio error "Exception from HRESULT: 0xC0204019"
Coordinator
Jul 20, 2016 at 7:29 PM
Hi. I verified the problem and you are right. The module was already supporting +4000 input, however there was an error in the UI code.
I've pushed on the new version, please download it and install it again.

Let me know if it's working or not,

Regards,
Alberto.
Jul 21, 2016 at 3:37 PM
I haven't been able to install the new version using the installer/copying the DLLs and using the GACManager/gacutil programs. The components are not visible in Visual Studio.

A request - when you release a new version - can you make sure the version number gets updated - it's still 1.1.0.0/1.1.000.0 (same as back in April)
Coordinator
Jul 21, 2016 at 4:25 PM
Hi,

What is your current environment? Which vs version? Which os? Which sql version?
Coordinator
Jul 24, 2016 at 1:14 AM
Found a missing reference for SQL 2014. Please download fixed version and let me know if is that working.

Regards,

Alberto.
Marked as answer by webking on 7/23/2016 at 5:14 PM
Sep 6, 2016 at 6:58 PM
I'm using VS 2015 Data Tools and SQL Server 2014. I performed a fresh install of your SSIS JSON component and unable to increase string sizes beyond 4000.

Below is a sample of a JSON string reformatted (from the JSON component's test results) where the third item contains a large amount of children array items. The first two items are processed successfully, but the third one causes the component to fail. Can you advise best practice to achieve a successful import or indicate if a solution is in the works?


Downloaded: {
"items": [
{
  "user": "ETL",
  "created": "2013-08-09T12:53:04Z",
  "updated": "2013-08-09T12:53:04Z",
  "id": 183,
  "assetTypeId": 2,
  "extId": 131485,
  "extSourceId": 2,
  "doc": {
    "Name": "xx1",
    "Type": "PhysicalServer",
    "Model": "HP ProLiant DL5",
    "rt_id": 1314,
    "Location": "Pod 5A (Ashwork)",
    "NlyteTag": "ln1",
    "ServiceTag": "332kkm2",
    "Description": "",
    "Environment": "stage",
    "LastUpdated": 1431012896,
    "Manufacturer": "HP ProLiant DL5",
    "SerialNumber": "4444xxx2333342"
  },
  "deleted": false,
  "links": {
    "parents": [],
    "children": []
  }
},
{
  "user": "ETL",
  "created": "2013-08-09T12:53:04Z",
  "updated": "2013-08-09T12:53:04Z",
  "id": 190,
  "assetTypeId": 44,
  "extId": 131,
  "extSourceId": 33,
  "doc": {
    "Name": "poop13455",
    "Type": "PhysicalServer",
    "Model": "PowerEdge R7",
    "rt_id": 13333,
    "Location": "Stage 5A (Ashwork)",
    "NlyteTag": "LN10122",
    "ServiceTag": "222JXgg3",
    "Description": "",
    "Environment": "stage",
    "LastUpdated": 1431013109,
    "Manufacturer": "Driver",
    "SerialNumber": "xxx333399"
  },
  "deleted": false,
  "links": {
    "parents": [],
    "children": []
  }
},
{
  "user": "ETL",
  "created": "2013-08-09T12:54:54Z",
  "updated": "2013-09-05T08:31:10Z",
  "id": 4221,
  "assetTypeId": 2,
  "extId": 333,
  "extSourceId": 2,
  "doc": {
    "OS": "xenserver",
    "Name": "dump1-xen9.sys.hms1.popin.xxxx",
    "Site": "hms",
    "Type": "PhysicalServer",
    "Model": "PowerEdge M6",
    "rt_id": 11333,
    "CPUnum": 16,
    "NetAddr": "22.100.33.555",
    "OSPatch": "90233c",
    "CPUmodel": "E5-2650",
    "PoolName": "dump1-hms1-popin",
    "Freshness": 201636,
    "MemoryMiB": 196562,
    "FormFactor": "Blade",
    "OSRevision": "6.5.0",
    "ServiceTag": "33339xx",
    "Description": "",
    "Environment": "stage",
    "LastUpdated": 1473064254,
    "Architecture": "x86_64",
    "BusinessUnit": [
      "Popsys"
    ],
    "ComputeUnits": 64.0,
    "MacAddresses": [
      "99:b9:9x:x9:99:29",
      "99:b9:9x:x9:99:2c"
    ],
    "Manufacturer": "Dell Inc.",
    "SerialNumber": "dddd15xd67axx8xxx40",
    "ServerStatus": "Running",
    "MemoryUsedMiB": 137216,
    "OSDescription": "XenServer 6.5.0-90233c",
    "AvailabilityZone": "hms1",
    "ComputeUnitsUsed": 67.0,
    "ProductionStatus": "InService",
    "XenLicenseExpire": "20300101T00:00:00Z",
    "OverSubscribedRatioCPU": 4.88
  },
  "deleted": false,
  "links": {
    "parents": [],
    "children": [
      {
        "assetId": 43,
        "displayName": "zdb2.sys.cnudev1.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736019,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 319,
        "displayName": "tls2.mgmt.jhms1.syseng.xxxx",
        "assetTypeId": 1,
        "linkId": 736018,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 242,
        "displayName": "tls1.xxeu.eupdev1.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736017,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 053,
        "displayName": "tls1.mgmt.sdumps3.coresys.xxxx",
        "assetTypeId": 1,
        "linkId": 736016,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 528,
        "displayName": "tls1.mgmt.cft3.coresys.xxxx",
        "assetTypeId": 1,
        "linkId": 736015,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 40,
        "displayName": "sui1.iac.cft3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736014,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 110,
        "displayName": "ssh2.jump.axshms1.syseng.xxxx",
        "assetTypeId": 1,
        "linkId": 736013,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 060,
        "displayName": "rnde4.tct.cft3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736012,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 313,
        "displayName": "rdb3.wmr.cnudev1.coresys.xxxx",
        "assetTypeId": 1,
        "linkId": 736011,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 20362,
        "displayName": "pxy1.tarteg.liveint3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736010,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 459,
        "displayName": "pxy1.mftape.cft3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736009,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 119,
        "displayName": "ops1.sys.sdumps3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736008,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 123,
        "displayName": "nsr2.sys.pjhms1.syseng.xxxx",
        "assetTypeId": 1,
        "linkId": 736007,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 45,
        "displayName": "mgr1.xxeu.eupqa2.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736006,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 110,
        "displayName": "mgr1.amgr.hms3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736005,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 447,
        "displayName": "fwd1.trunk.gmihms1.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736004,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 656,
        "displayName": "evt1.xxeu.eupdev3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736003,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 88,
        "displayName": "edb1.xxeu.eupqa2.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736002,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 57,
        "displayName": "ctx1.xxeu.eupqa3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736001,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 197,
        "displayName": "cxxaestro17.hoasta.gmihms1.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 736000,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 29,
        "displayName": "cch7.xxxx.hms1.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 7999,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 217,
        "displayName": "apt5.xxeu.hms3.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 735998,
        "linkRuleId": 9,
        "parentsCount": 2
      },
      {
        "assetId": 005,
        "displayName": "app1.xxeu.eupdev2.xxxsys.xxxx",
        "assetTypeId": 1,
        "linkId": 735997,
        "linkRuleId": 9,
        "parentsCount": 2
      }
    ]
  }
}
],
"page": {
"pageNumber": 0,
"totalPages": 1,
"totalCount": 3,
"resultsPerPage": 3
}
}
Sep 6, 2016 at 7:00 PM
One clarifying point...

I can set the string size beyond 4000, but the component generates an error immediately after execution.
Coordinator
Sep 27, 2016 at 3:49 PM
Hi,

first of all please accept my apologies regarding this problem. I found and solved this issue. Please download and install the component again. It should be gone. Please let me know if there is anything wrong.
Marked as answer by webking on 9/27/2016 at 7:49 AM