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

Unable to get root values

Mar 30, 2016 at 4:50 PM
Hi

First i want to say that i think that this is a very good addin even if i've just started to use it.
Keep it up and i'll buy u some beers.

I'm trying to figure out how to use this and the first try went fine but now i have ran in to a problem.

The Json i have looks something like this:
{
  "firstlink": "http://myfirstwebpage.com",
  "value": [
    {
      "name": "test",
      "desc": "Test desc",
      "amount": 234,
      "typ": "cost"
    },
   
    {
      "name": "test2",
      "desc": "Test2 desc",
      "amount": 345,
      "typ": "cost"
    },
    ...
    ...
    ...
  ],
  "nextlink": "http://nynextwebpage.com"
}
I have no problem getting the "value" part, that works perfect. in that test i set "value" as root.
But how do i get the "firstlink" and "nextlink".
I have tried to set root to nothing i have tried to set it to "$" but i don't get the http link.

How to i do that?

Best regars
Ronny
Coordinator
Mar 30, 2016 at 7:08 PM
Edited Mar 30, 2016 at 7:13 PM
Hi Ronny,

It is simple. You have to do the following:
  1. Leave the Root empty, this means the component will use the beginning of the json as root.
  2. Since the root is a JsonObject, you have to tell the component you are now parsing a json object.
If you ever wanted to have, in a single table, both the values and the next-last links, you can use the json filter and union components.

Here it is how I did:

Image


Image

At the moment it is still not so easy to work with this component. I am going to make it more intuitive, but time is what I need!
Marked as answer by webking on 3/30/2016 at 2:18 PM
Mar 30, 2016 at 7:41 PM
Okay...

I was doing just that except for the fact that i only had the nextlink as fields and nothing else.
The thing is that the nextlink is to another page that i need to get to get more data.

Do i need to point out all the fields or can i just enter one or two of the fields?

Regards
Ronny


2016-03-30 20:08 GMT+02:00 webking <[email removed]>:

From: webking

Hi Ronny,

It is simple. You have to do the following:
  1. Leave the Root empty, this means the component will use the beginning of the json as root.
  2. Since the root is a JsonObject, you have to tell the component you are now parsing a json object.
If you ever wanted to have, in a single table, both the values and the next-last links, you can use the json filter and union components.

Here it is how I did:

Image
TEXT

Image
TEXT

At the moment it is still not so easy to work with this component. I am going to make it more intuitive, but time is what I need!

Read the full discussion online.

To add a post to this discussion, reply to this email ([email removed])

To start a new discussion for this project, email [email removed]

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Coordinator
Mar 30, 2016 at 10:18 PM
Hi,

you can put the only fields you are interested into. For sake of clarity, I've specified all the fields.
Mar 31, 2016 at 11:03 AM
Hmmm

I think I know what the problem is.

I didn't specify the name of the field exactly like it was just because i don't have the file localy.

So the accutal name of the field has a "." in it and that i think makes it a problem with xPath.

So the json looks more like this:
{
  "first.link": "http://myfirstwebpage.com",
  "value": [
    {
      "name": "test",
      "desc": "Test desc",
      "amount": 234,
      "typ": "cost"
    },
   
    {
      "name": "test2",
      "desc": "Test2 desc",
      "amount": 345,
      "typ": "cost"
    },
    ...
    ...
    ...
  ],
  "next.link": "http://nynextwebpage.com"
}
So notice that the "nextlink" is acctually "next.link".

I tried to incase the name in the JSON Field Name with "" and [] but it didn't help.
"next.link" just does not give me anything, same as before, but no errors.
[next.link] gives me an error instead and complains about xPath.

Is there a way to solve this or is "." not supported in the field names in the JSON source yet?

Regars
Ronny
Coordinator
Mar 31, 2016 at 12:28 PM
Hi,

have you tried to escape the jsonpath expression? Have a look at the following link:
https://groups.google.com/forum/#!topic/jsonpath/7YvgXWP1_7Y


So, in you case I would try the following as json expressions:
$.['first.link']
$.['next.link']

At the moment I cannot test it out. Let me know if it still doesn't work so I give it a try when I get back home this evening.

Regards,

Alberto.
Mar 31, 2016 at 12:36 PM
Perfect, and thanks for the quick answer, i'll test it now and get back to you.

Regars
Ronny

2016-03-31 13:28 GMT+02:00 webking <[email removed]>:

From: webking

Hi,

have you tried to escape the jsonpath expression? Have a look at the following link:
https://groups.google.com/forum/#!topic/jsonpath/7YvgXWP1_7Y


So, in you case I would try the following as json expressions:
$.['first.link']
$.['next.link']

At the moment I cannot test it out. Let me know if it still doesn't work so I give it a try when I get back home this evening.

Regards,

Alberto.

Read the full discussion online.

To add a post to this discussion, reply to this email ([email removed])

To start a new discussion for this project, email [email removed]

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Mar 31, 2016 at 12:41 PM

YES IT WORKS.

Thanks a lot for the help.

And again, thanks for a great add on to SSIS.
Keep it up.

Best regards
Ronny

Den 31 mar 2016 1:36 PM skrev "Ronny Eriksson" <[email removed]>:
Perfect, and thanks for the quick answer, i'll test it now and get back to you.

Regars
Ronny

2016-03-31 13:28 GMT+02:00 webking <[email removed]>:

From: webking

Hi,

have you tried to escape the jsonpath expression? Have a look at the following link:
https://groups.google.com/forum/#!topic/jsonpath/7YvgXWP1_7Y


So, in you case I would try the following as json expressions:
$.['first.link']
$.['next.link']

At the moment I cannot test it out. Let me know if it still doesn't work so I give it a try when I get back home this evening.

Regards,

Alberto.

Read the full discussion online.

To add a post to this discussion, reply to this email ([email removed])

To start a new discussion for this project, email [email removed]

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Mar 31, 2016 at 9:33 PM
Edited Mar 31, 2016 at 9:33 PM
Hi,

I have problems with getting data form json and i would appreciate your help.
My JSON looks like this:

{
"members": [
{
  "subscribe": false,
  "facebookData": {},
  "googleData": {},
  "devices": [
    {
      "device": "xxxxx-xxxx-xxx-xxx-xxx",
      "platform": "Android"
    }
  ],
  "username": "test_deu",
  "email": "test_deu@xxxx.yy",
  "id": "56fd5e071f43671xxxxxxxxxxx",
  "createdAt": "2015-03-31T17:27:35.586Z",
  "updatedAt": "2015-03-31T17:27:35.586Z"
},
I set up root to memebers and I get all the values from members. But I cannot get value from devices. I have tried with diffreent expressions but the result is always null.
If I put devices I get the values and I can later in SSIS with JSON filter get the vaues, but again I cannot combine it to one row.
What would be the best is that I would get all data in one step so I can combine it to one row.
I have tried:
  • $.['devices.device']
  • $.members[].devices[].device
But with no luck.

Thank you in advance

Borut
Coordinator
Mar 31, 2016 at 10:54 PM
Edited Mar 31, 2016 at 10:54 PM
Hi,

have a look at this.

Image

Regards,

Alberto.
Apr 1, 2016 at 6:58 AM
Thanky webking...you rock!
I have tried everything except this .. :)


BR, Borut
Apr 1, 2016 at 6:59 AM
Edited Apr 1, 2016 at 7:06 AM
One more thing. Why I am getting only 100 rows out of component. Is there any limitation?
Hm, I cannot paste the image..


Everything OK...I found out where is the problem :)
Coordinator
Apr 1, 2016 at 7:07 AM

Hi,

No litation is hardcoded in the component. It must have something to do with your web requests, maybe pagination.

Regards,

Alberto.

Apr 1, 2016 at 9:34 AM
Hi Borut

My base problem was just that, getting all pages.
i Only got 1000 rows, but in the data i got, the last part is the next page where i got more data.

So check if you get a "nextpage" link in the end of your data.

Regards
Ronny
Apr 1, 2016 at 10:05 AM
One more thing...
I am trying to get for example 10 pages of data. Each page has 1000 items. The component is working by passing one page at the time. It would be great to have iteration and passing parameters to the component.
Coordinator
Apr 1, 2016 at 6:08 PM
Hi,

I am currently working on this feature for the future release. At the moment this may be possible using variables. In the next release I am planning to support a "input data lane" for parameters, alongside much code refactoring and support for SQL Server 2016.
However it takes time and resources to do that, so I'm not sure when this future version will be ready. Moreover I'm spending much time trying to support people on this forum and this takes time as well!
Apr 1, 2016 at 10:11 PM
Edited Apr 1, 2016 at 10:19 PM
Hi

I know how development is going...and I have to say, that you have done a huge job and make life easier for many of us :)

I have one more issue and I would like to ask you to help me. I have such JSON

[
  {
    "id": "56376xxxxxxxxxx",
    "name": "Point 1",
    "test": [
      {
        "serialnumber": "abc",
        "config": {
          "major": "38519",
          "protocol": "test"
        },
        "id": "56421d2xxxxxxxx",
        "createdAt": "2012-11-10T16:36:50.427Z",
        "updatedAt": "2012-11-10T16:36:50.427Z"
      }
    ]
  }
  ]
And If I use JSON Source component I'm keep getting the error:
[JSON Source Component] Error: Error reading JObject from JsonReader. Current JsonReader item is not an object: StartArray. Path '', line 1, position 1. at Newtonsoft.Json.Linq.JObject.Load(JsonReader reader, JsonLoadSettings settings)
at Newtonsoft.Json.Linq.JObject.Load(JsonReader reader)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader _sr, PipelineBuffer buffer, RootType rootType)

[SSIS.Pipeline] Error: The PrimeOutput method on "JSON Source Component" returned success, but did not report an end of the rowset. There is an error in the component. It should have reported an end-of-row. The pipeline will shut down execution to avoid unpredictable results.


I was trying with http://jsonpath.com/. With this tool I get everything what I need..

What do I have to set up for root ? I left it blank...

Thank you
Borut
Coordinator
Apr 2, 2016 at 7:44 AM
The error is clear. The objecy starting token is an array and not an object. Change the value of the combobox in the columns tab, next to the root field, put "jsonarray".

Alberto.