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

column mapping question

Topics: Example needed
Oct 20, 2016 at 2:08 AM
I have JSON files that list Certifications that an employee has. An employee’s file might have any number of “associateCertifications” as JSON array elements. So I can return values for element [0], and [1], and [2] etc. This would be really tedious to map everything for element [0], then repeat it for [1], etc. Also I don’t know how many Certifications an employee might have, so I would have to map out to [xx]!
I feel I’m missing something. Is there a way to tell the tool to “return all”?
Here is a partial dump of a file.
{
"associateKSAOCS": [
    {
        "associateCertifications": [
            {
                "itemID": "333984925_729",
                "certificationID": {
                    "idValue": "02890287"
                },
                "certificationNameCode": {
                    "codeValue": "CST",
                    "longName": "Certified Survey Technician"
                },
                "categoryCode": {
                    "codeValue": "C",
                    "shortName": "Certification"
                },
                "issuingParty": {
                    "nameCode": {
                        "codeValue": "NSPS",
                        "shortName": "NSPS"
                    }
                },
                "firstIssueDate": "2008-06-30",
                "expirationDate": "2017-06-30"
            },
            {
                "itemID": "333984925_731",
                "certificationID": {
                    "idValue": "0289-0287"
                },
                "certificationNameCode": {
                    "codeValue": "MSOFTCSE",
                    "longName": "Microsoft Certified System Engineer"
                },
                "categoryCode": {
                    "codeValue": "C",
                    "shortName": "Certification"
                },
                "issuingParty": {
                    "nameCode": {
                        "codeValue": "Microsoft",
                        "shortName": "Microsoft"
                    }
                },
                "firstIssueDate": "2001-05-31",
                "expirationDate": "2033-12-31"
            },
.......
Coordinator
Oct 20, 2016 at 11:29 PM
Edited Oct 20, 2016 at 11:41 PM
Hi!

First of all please download and install again the component. I've updated it so that it's now working in your specific case (there was a small bug I had to fix).

Yes, there is a way for doing that. The key idea is to use a JSON source component as ROOT and leave all nested data as "RAW". Then use the filter to "explode" the nested data.

Look at the image here:
Capture.png

Also, I have uploaded am example of the project I've used to demonstrate this functionality. Give it a look: Project

Is that clear?
Marked as answer by webking on 10/20/2016 at 3:33 PM
Oct 21, 2016 at 6:32 PM
That works! Thanks.
Oct 26, 2016 at 7:02 PM
Problem with new version
I went to install the new version on my production system and ran into a problem with a previously working DTSX. After playing around I finally figured out that some of the employee entries in the JSON files where missing the “maritalStatusCode”, and the new version was failing on them. The previous version seems to just skip over them and pushed a NULL thru to the output, which was fine. Reinstalling the previous version got production back to where it needed to be.

I’ve created simplified versions of the DTSX that dumps to a file rather than a database to show the problem. FieldsAlwayThere.dtsx works cause it’s parsing just File Number. In FieldsMissing.dtsx I added the maritalStatusCode which makes it fail.

I created this as a 2016 solution at C:\test\WorkerTest so hopefully it will be easy for you to load and try. In the solution folder you will find worker00.json that I’m trying to parse. I also have a formatted version of that in workers json formated.docx where I’ve highlighted some entries.

NOTE: I tried to reply to the email you sent me, with a ZIP of the above attached. Not sure if you got that or how else to get it to you.
Coordinator
Oct 27, 2016 at 7:28 PM
Hi,

I'll give it a try next week. Can you please provide a link via dropbox? I didn't find your email. I think my antispam filter just trashed it.
Oct 28, 2016 at 12:20 PM
Don’t have a DropBox. But following are links to shared OneDrive ZIP’s containing some test SSIS Solutions. The WorkerTest is for the problem discussed above. CertsText is the project I was working on that you created the 10/20 version for. I’m having odd problems with it too.
In both ZIP’s see Word Docx with more details.

here is link to WorkerTest
WorkerText

here is link to CertsText
CertsText
Nov 3, 2016 at 8:49 PM
does 11/3/2016 update do anything for this?
Jan 9 at 4:39 PM
Hi Alberto,

First; thank you for your continued effort! Much appreciated.
As my question is column-mapping related I thought to reply on this one. Hope jbrown08322 don't mind.

I have to get jsonsource working :-)
I'm halfway there; authentication is working as to be expected see my reply on:
https://jsonsource.codeplex.com/discussions/660109

At this point I have an issue with the response on my (web-)url-request. I looks like jsonsource-component cannot detect the columns?
Can you provide some assistence in how I should go about it? I've pasted an example from what I get back in the ComponentTester:
Downloaded: {
  "skip": 0,
  "take": 100000,
  "rows": [
    {
      "Werkgever": "01",
      "Naam_werkgever": "De Werkgever",
      "Medewerker": "69",
      "Achternaam": "Janssen",
      "Voorletters": "H.",
      "Roepnaam": "HNK",
      "Voorvoegsel": "van den",
      "Geboortenaam": "HNK",
      "Voorvoegsel_partner": "de",
      "Geboortenaam_partner": "Vriendelijke",
      "Naamgebruik": "Geboortenaam",
      "Leeftijd": 71,
      "Straat": "Kon Wilhelminastraat",
      "Huisnummer": 97,
      "Toev._aan_huisnr.": null,
      "Postcode": "5183 XB",
      "Woonplaats": "Grave",
      "Land": "NL",
      "Nationaliteit": "Nederlandse",
      "Geslacht_code": "V",
      "Geslacht": "Vrouw",
      "Datum_in_dienst": "1981-09-01T00:00:00Z",
      "Dienstverband": 1,
      "Dienstbetrekking": "Vaste medewerkert",
      "Type_contract": "Onbepaalde tijd",
      "Organisatorische_eenheid": "1199",
      "Omschrijving": "Werkvloer - Medewerker",
      "Functiecode": "119",
      "Functie": "Medewerker - Werkvloer",
      "Functiegroep": null,
      "Cluster": null,
      "Kostenplaats": "-",
      "Burgerlijke_staat": "Overig",
      "Leidinggevende": "1234567",
      "Leidinggevende_naam": "KLS Vaak",
      "Geboortedatum": "1986-08-30T00:00:00Z",
      "In_dienst__i.v.m._dienstjaren_": "1987-09-01T00:00:00Z",
      "Begindatum": "1987-09-01T00:00:00Z",
      "Einddatum": "2016-09-30T00:00:00Z",
      "Percentage": 100,
      "Aantal_FTE": 1,
      "Parttime____": 100,
      "Aantal_uren_per_week": 36
    },
    {
      "Werkgever": "01",
      "..." : "..."
     }
     ]
     }