Can't Get the data with the Json Source Component

Mar 7, 2016 at 1:35 PM
I just start to try get the data from a web service that have the information in json format.

this is the structure:
[
{
"_links": {
  "self": {
    "href": "http://api.football-data.org/v1/soccerseasons/394"
  },
  "teams": {
    "href": "http://api.football-data.org/v1/soccerseasons/394/teams"
  },
  "fixtures": {
    "href": "http://api.football-data.org/v1/soccerseasons/394/fixtures"
  },
  "leagueTable": {
    "href": "http://api.football-data.org/v1/soccerseasons/394/leagueTable"
  }
},
"id": 394,
"caption": "1. Bundesliga 2015/16",
"league": "BL1",
"year": "2015",
"currentMatchday": 25,
"numberOfMatchdays": 34,
"numberOfTeams": 18,
"numberOfGames": 306,
"lastUpdated": "2016-03-06T16:50:30Z"
},


URI:http://api.football-data.org/v1/soccerseasons/?season=2015
  1. the first step was connect to the URI.
    opcions: Direct input, GET thats works fine!(the preview works)
  2. put all the columns all as string.
    options:
     Array Root Path C:\Data\ (¿what is this?)
     Columns:
         _links.self.href
         _links.teams.href
         _links.fixtures.href
          _links.leagueTable.href
          id
          caption
          league
          year
         currentMatchday
         numberOfMatchdays
          numberOfTeams
          numberOfGames
         lastUpdated
    
AND when i execute the package i have this error (i think is for the array Root Path what is this???):

[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)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader _sr, PipelineBuffer buffer)
[JSON Source Component] Error: An error has occurred: Error occurred: 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)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader _sr, PipelineBuffer buffer).
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader _sr, PipelineBuffer buffer)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers).

Another point is when i try to put some Http Parameters to put the api-key in a parameter
the component automatically is closed without show any form.

Kind Regards,
Julio.
Mar 9, 2016 at 12:14 PM
HI,
Someone can help me with this please?
Coordinator
Mar 9, 2016 at 1:49 PM
Hi,

The array root path represents the path to the array data to be parsed. It has nothing to do with the filesystem.
In your case, the result given by the url is already an array, and it is probably what you need. So, just leave that field empty.

If this does not solve the problem, do you mind posting a screenshot about the current component configiration?
Mar 9, 2016 at 3:43 PM
Edited Mar 9, 2016 at 3:44 PM
Hi,
I just make a test without the array and i have the same problem.
this is my configuration thanks!!!
https://www.dropbox.com/s/iewj59ywhedvk6k/1.JPG?dl=0
https://www.dropbox.com/s/qyeubj9ahnm6s6q/2.JPG?dl=0
https://www.dropbox.com/s/8eim8v2rxx1bqdy/3.JPG?dl=0
Mar 9, 2016 at 3:49 PM
I just edit the post becouse the images links was broken
thank you for your help!!
Kind Regards
Coordinator
Mar 9, 2016 at 11:52 PM
Hi, you were right. The component had some problems in this new version.
So, I've fixed the HTTP Params error, along side the error you are receiving complaining the Array/Object casting.

Please, uninstall the previous version (remove dll from gac and from installation path), then download again the DLL Pack from the download section and install them again.

Now, a new combobox should appear into the Columns Section, just next to the Json root path. There you can specificy if the root expected is a Json Object or a Json Array. On your case it will be a JsonArray. So select json array and run the test.

Let me know!
Mar 10, 2016 at 10:16 AM
Hi alberto,
first thanks form the quick answer,
now i just had unisstall- and install the new version of dll files, but now i can´t see the component in ssis tool box

this was the steps
  1. unisstall using the gac manager
  2. go to the folders program files x32 and program files x64 and delete de dll
  3. copy the dll to the folders
    C:\Program Files (x86)\Microsoft SQL Server\120\DTS\PipelineComponents
    C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents
  4. use the Gac manager and i can see the assembly
    5 open ssis but is not in the ssis tool box
im using visual studio 2013 ultimate update 4 and sql server 2014
the are some images of the proccees.

https://www.dropbox.com/s/qsd8go51bxhjeek/4.JPG?dl=0
https://www.dropbox.com/s/lkf7us6jj8tgd21/5.JPG?dl=0

thanks for the help!!
Coordinator
Mar 10, 2016 at 8:09 PM
Hi,

I've added a new version with Guided Installation support. Look in the download section and use that installer, it should make the trick.

Let me know,

Alberto.
Mar 11, 2016 at 8:18 AM
Hi Alberto,
I just uninstall the previous version by the GAC manager and delete the dll from the directory.
Use the new installer and they are in the GAC manager assemblies but still not seen in the ssis tool box ,
you know how i can make visual studio reproceess all assemblies from the ssis componets?

maybe this update the components

a few captures of the procees

https://www.dropbox.com/s/yv6okh4nyfifyvv/Capture.JPG?dl=0
https://www.dropbox.com/s/wanqvk4uftu53dz/Capture2.JPG?dl=0
https://www.dropbox.com/s/ozjgg3fleqp5mkl/Capture3.JPG?dl=0

Kind Regards,
Julop
Coordinator
Mar 11, 2016 at 12:17 PM
Hi Julop,

This is very strange. I might have changed something in the new release that has broken something in SQL 2014. I'm so sorry for that. You can't imagine how difficult it is to make one single component to work correctly with many SQL versions/architectures.

That said, I promise I'll look at that this evening, once I get home from work. Fortunately I have a VM with your environment, so should be easy to reproduce the error.

I'll let you know asap,

Alberto.
Mar 11, 2016 at 2:40 PM
Hi Alberto,
thanks for the effort,
and also i want to say that you are doing a great job with this component

Kind Regards,
Julio
Coordinator
Mar 11, 2016 at 7:54 PM
Edited Mar 11, 2016 at 7:59 PM
Hi Julop,

I really think I've fixed it now. I've tried your configuration, i.e.
  • Windows Server 2012 +
  • MSSQL 2014
  • .NET 4.0
  • VS 2013 BIDS
I've updated both the installer and the DLL pack. Now even the automated installer should make the trick. If it doesn't, please remove the DLLs from gac and from installation dirs manually and then try again with the installer.

Please get back to me when done, it is important for me to understand if the problem has been finally solved!

Regards,

Alberto.

EDIT: I really appreciate your graditude, and your help is crucial as well!
Mar 12, 2016 at 8:00 PM
Hi Alberto,
I the installer didnt wok, but the manual installer works fine!

and all the new features are great!!
https://www.dropbox.com/s/hyd2pp9epsbhdpp/Done.JPG?dl=0

i just bought you 5 beers thanks for this component!!

i'mworking in my final master proyect and this component just has saved me much time!!

thanks a lot !!!

Julio
Marked as answer by webking on 3/14/2016 at 4:47 AM
Coordinator
Mar 14, 2016 at 11:47 AM
Hi jgranados,

Thank you for the donation and for the great help you have been providing.
In the future, feel free to get in touch again!

Regards,

Alberto.
Mar 20, 2016 at 3:03 PM
Hi Alberto,
I having a problem getting a jsonObject that inside have a vetor.

this is the example :
the vector is standing and inside have some objets.
{
  "_links": {
    "self": {
      "href": "http://api.football-data.org/v1/soccerseasons/399/leagueTable/?matchday=30"
    },
    "soccerseason": {
      "href": "http://api.football-data.org/v1/soccerseasons/399"
    }
  },
  "leagueCaption": "Primera Division 2015/16",
  "matchday": 30,
  "standing": [
    {
      "_links": {
        "team": {
          "href": "http://api.football-data.org/v1/teams/81"
        }
      },
      "position": 1,
      "teamName": "FC Barcelona",
      "crestURI": "http://upload.wikimedia.org/wikipedia/de/a/aa/Fc_barcelona.svg",
      "playedGames": 29,
      "points": 75,
      "goals": 84,
      "goalsAgainst": 22,
      "goalDifference": 62,
      "wins": 24,
      "draws": 3,
      "losses": 2,
      "home": {
        "goals": 54,
        "goalsAgainst": 10,
        "wins": 14,
        "draws": 1,
        "losses": 0
      },
      "away": {
        "goals": 30,
        "goalsAgainst": 12,
        "wins": 10,
        "draws": 2,
        "losses": 2
      }
    },
the component not suports json objects vetors?

kind regards
Julio
Mar 21, 2016 at 8:15 PM
HI,
Someone can help me with this please?
Coordinator
Mar 23, 2016 at 9:25 AM
Edited Mar 23, 2016 at 9:27 AM
Hi,

first of all there was a problem with the latest release. I have updated both the installer and the DLL pack with the fix. Please reinstall the component. After you do that, make sure to remove any previous Json source component from the current project, save it and then open it again. This happens whenever you update a component in the Visual Studio environment.

About your problem, here is how I extract information from the json array "standing"



Image
Mar 31, 2016 at 10:10 PM
Thanky webking...you rock!
I have tried everything except this .. :)

BR, Borut