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

Cant get any Columns or Values

Apr 7, 2016 at 2:57 PM
Hi,
I know I must be doing something wrong, just don't know what... I have a downloaded JSON file, that I try to pars, so that I can import into SQL.

Structure is as follows:
Downloaded: {
"city": {
"id": 1283240,
"name": "Kathmandu",
"findname": "KATHMANDU",
"country": "NP",
"coord": {
  "lon": 85.316666,
  "lat": 27.716667
},
"zoom": 7
},
"time": 1394871602,
"main": {
"temp": 297.15,
"pressure": 1020,
"humidity": 33,
"temp_min": 297.15,
"temp_max": 297.15
},
"wind": {
"speed": 3.1,
"deg": 240
},
"clouds": {
"all": 40
},
"weather": [
{
  "id": 802,
  "main": "Clouds",
  "description": "scattered clouds",
  "icon": "03d"
}
]
}

I add JSON field Name = city.. nothing happens, I add city.name nothing happens
Everything I try gets me nothing, so what am I doing wrong?
I have tried to put Donloaded in the path to JSON Array/Object...

Any help i appreciated.
Apr 7, 2016 at 4:25 PM
Got it to work now, set it with JSON field Name = $, but I cant get it to run,... it says no input columns available..

Stumped again.
Coordinator
Apr 7, 2016 at 6:17 PM
Hi,

your spotted a bug and I've already corrected that. You were right, there was something wrong with the new version of the software: when no input lane is attached nothing happens. Now I've fixed that. Please download the installer again and perform again the installation. Later, make sure to open the project, delete all the JSONSource components and save it. After that you might re-open that project and start putting JSONSource components again.

Let me know if you make it!

P.S. Input mapping should be:
city.name ...
city.ID...
and so on.
Apr 8, 2016 at 9:46 AM
Hi again,

Thanks for fixing the issue with no input columns, but it seems that there is something else off as well. When I try to run it, all it loads into the flatfile destination is one row, and there over 12000 rows . Do you have any idea what that is about?
I also still get the no input columns available when I right click and choose advanced editor.

Thank you in advance.
Coordinator
Apr 8, 2016 at 6:51 PM
Forget about the advanced editor, that is a warning and you can get rid of it. Simply use the standard editor mode which is based on my custom UI.

You provided me an input with only one result. I've modified the input and tested the component, with the following input data:
{  
   "results":[  
      {  
         "city":{  
            "id":1283240,
            "name":"Kathmandu",
            "findname":"KATHMANDU",
            "country":"NP",
            "coord":{  
               "lon":85.316666,
               "lat":27.716667
            },
            "zoom":7
         },
         "time":1394871602,
         "main":{  
            "temp":297.15,
            "pressure":1020,
            "humidity":33,
            "temp_min":297.15,
            "temp_max":297.15
         },
         "wind":{  
            "speed":3.1,
            "deg":240
         },
         "clouds":{  
            "all":40
         },
         "weather":[  
            {  
               "id":802,
               "main":"Clouds",
               "description":"scattered clouds",
               "icon":"03d"
            }
         ]
      },
      {  
         "city":{  
            "id":1283241,
            "name":"Rome",
            "findname":"ROME",
            "country":"IT",
            "coord":{  
               "lon":11.11,
               "lat":22.11
            },
            "zoom":7
         },
         "time":1394871602,
         "main":{  
            "temp":297.15,
            "pressure":1020,
            "humidity":33,
            "temp_min":297.15,
            "temp_max":297.15
         },
         "wind":{  
            "speed":3.1,
            "deg":240
         },
         "clouds":{  
            "all":40
         },
         "weather":[  
            {  
               "id":802,
               "main":"Clouds",
               "description":"scattered clouds",
               "icon":"03d"
            }
         ]
      }
   ]
}
Everithing worked. See the image attached:

Image

If you still have problems, please provide the whole (or at least a couple of object) input of the component.

Alberto.
Apr 8, 2016 at 7:47 PM

Hi,

The file structure in your example is a bit different than what I have. I can make it like yours if need be, so thought I'd give you what I have to work with (I have attached some code), if this thread get to chaotic, please delete. As mentioned, I could not make it work, I had put $ as the root then to create columns I started with city.id and so on.

Any help is appreciated.

{"city":{"id":1283240,"name":"Kathmandu","findname":"KATHMANDU","country":"NP","coord":{"lon":85.316666,"lat":27.716667},"zoom":7},"time":1394871602,"main":{"temp":297.15,"pressure":1020,"humidity":33,"temp_min":297.15,"temp_max":297.15},"wind":{"speed":3.1,"deg":240},"clouds":{"all":40},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}]}
{"city":{"id":3632308,"name":"Merida","findname":"MERIDA","country":"VE","coord":{"lon":-71.144997,"lat":8.598333},"zoom":8},"time":1394871602,"main":{"temp":288.64,"temp_min":288.64,"temp_max":288.64,"pressure":835.59,"sea_level":1023.46,"grnd_level":835.59,"humidity":99},"wind":{"speed":0.5,"deg":183.004},"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}],"rain":{"3h":0}}
{"city":{"id":1280737,"name":"Lhasa","findname":"LHASA","country":"CN","coord":{"lon":91.099998,"lat":29.65},"zoom":6},"time":1394871602,"main":{"temp":280.79,"temp_min":280.79,"temp_max":280.79,"pressure":613.65,"sea_level":1018.68,"grnd_level":613.65,"humidity":12},"wind":{"speed":0.75,"deg":156.504},"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01d"}]}
{"city":{"id":745042,"name":"İstanbul","findname":"İSTANBUL","country":"TR","coord":{"lon":28.983311,"lat":41.03508},"zoom":1,"langs":[{"bg":"Истанбул"},{"el":"Ισταμπούλ"},{"en":"Istanbul"},{"es":"Provincia de Estambul"},{"ja":"イスタンブール"},{"link":"http:\/\/en.wikipedia.org\/wiki\/Istanbul_Province"},{"no":"Istanbul"},{"ru":"Стамбул"},{"tr":"İstanbul"},{"zh":"伊斯坦布尔"}]},"time":1394871602,"main":{"temp":283.42,"pressure":1021,"temp_min":283.15,"temp_max":284.15,"humidity":87},"wind":{"speed":2.57,"gust":2.57,"deg":220},"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01d"}]}
{"city":{"id":3496831,"name":"Mao","findname":"MAO","country":"DO","coord":{"lon":-71.078133,"lat":19.551861},"zoom":10},"time":1394871602,"main":{"temp":295.14,"temp_min":295.14,"temp_max":295.14,"pressure":1020.65,"sea_level":1028.41,"grnd_level":1020.65,"humidity":100},"wind":{"speed":3.1,"deg":71.504},"clouds":{"all":88},"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10n"}],"rain":{"3h":0.5}}
{"city":{"id":523523,"name":"Nalchik","findname":"NALCHIK","country":"RU","coord":{"lon":43.618889,"lat":43.498058},"zoom":8},"time":1394871602,"main":{"temp":286.15,"pressure":1018,"humidity":30,"temp_min":286.15,"temp_max":286.15},"wind":{"speed":2,"deg":70},"clouds":{"all":90},"weather":[{"id":804,"main":"Clouds","description":"overcast clouds","icon":"04d"}]}

Coordinator
Apr 8, 2016 at 10:17 PM
The reason why it is not working is because that json is not valid. If you copy-paste that json data into any json validator, you'll get the error. Give this a try and see what happens when you try to validate your input.
As you'll discover, it says "Multiple JSON Root elements". JSON, like XML, supports only one root element. This means that the returned data should either be enclosed into another object or into an array.

However, there is a workaround. If you already have the json data downloaded into file A you might do the following:
  1. Attach a flat file source to that file. Configure it with a single output column containing all the input rows. To do so, you might want to leave the row delimiter to {CR}{LF} and leave the column delimiter blank
  2. Attach the output lane into a JSONFilter component, that takes the input column and provides the parsing feature. See the following image for more info.
Image

If you still wish to download the json by performing am HTTP request directly from within the dataflow, you can use a JSONSource element providing output for the filter, like the following:

Image

I could not test the second case, since I have no idea what is the URL of the webservice you are using.

Let me know if its clearer to you know,

Alberto.
Apr 11, 2016 at 9:24 AM
Edited Apr 11, 2016 at 1:04 PM
Hi again,

Thank you for the explanation and workaround...

Problem with using a flatfile source is that the file contains quite a bit of data that I don't, so with the flat file source, it becomes very sluggish as it will only process so many rows before it stops to let the rest of the steps catch up. And I only really need a part of whats in the file.. the rest can be conditionally discarded.


I wrote a script to prepend a array to encapsulate the entire string with {"Result":[

then apply a comma between each {city} array.. etc.

But it seems that there is something with the plugin or on my PC that causes the plugin to loose all input columns and I seem to be left with very little choice but to remove and reinstall the plugin again

Here is the configuration_model_object..
{"DataSource":{"SourceUri":"file:///C:/Private/Dev/JSON/weather_14.json","FromVariable":false,"VariableName":null,"HttpParameters":[],"CookieVariable":null,"WebMethod":"GET"},"DataMapping":{"_ioMap":{"city.id":{"InputFieldPath":"city.id","InputFieldLen":0,"OutputColName":"ID","OutputJsonColumnType":0},"city.country":{"InputFieldPath":"city.country","InputFieldLen":255,"OutputColName":"Country","OutputJsonColumnType":1},"city.name":{"InputFieldPath":"city.name","InputFieldLen":255,"OutputColName":"City","OutputJsonColumnType":1},"city.coord.lat":{"InputFieldPath":"city.coord.lat","InputFieldLen":0,"OutputColName":"Latitude","OutputJsonColumnType":0},"city.coord.lon":{"InputFieldPath":"city.coord.lon","InputFieldLen":0,"OutputColName":"Longitude","OutputJsonColumnType":0},"time":{"InputFieldPath":"time","InputFieldLen":0,"OutputColName":"Time","OutputJsonColumnType":0},"main.temp":{"InputFieldPath":"main.temp","InputFieldLen":0,"OutputColName":"Temp","OutputJsonColumnType":0},"main.temp_min":{"InputFieldPath":"main.temp_min","InputFieldLen":0,"OutputColName":"Temp_min","OutputJsonColumnType":0},"main.temp_max":{"InputFieldPath":"main.temp_max","InputFieldLen":0,"OutputColName":"Temp_max","OutputJsonColumnType":0},"main.pressure":{"InputFieldPath":"main.pressure","InputFieldLen":0,"OutputColName":"Pressure","OutputJsonColumnType":0},"main.humidity":{"InputFieldPath":"main.humidity","InputFieldLen":0,"OutputColName":"Humidity","OutputJsonColumnType":0},"wind.speed":{"InputFieldPath":"wind.speed","InputFieldLen":0,"OutputColName":"Wind_speed","OutputJsonColumnType":0},"wind.gust":{"InputFieldPath":"wind.gust","InputFieldLen":0,"OutputColName":"Wind_gust","OutputJsonColumnType":0},"wind.deg":{"InputFieldPath":"wind.deg","InputFieldLen":0,"OutputColName":"Wind_direction","OutputJsonColumnType":0},"clouds.all":{"InputFieldPath":"clouds.all","InputFieldLen":0,"OutputColName":"Clouds","OutputJsonColumnType":0},"$.weather[:].main":{"InputFieldPath":"$.weather[:].main","InputFieldLen":255,"OutputColName":"WeatherCond","OutputJsonColumnType":1},"$.weather[:].description":{"InputFieldPath":"$.weather[:].description","InputFieldLen":255,"OutputColName":"WeatherDetails","OutputJsonColumnType":1},"rain.3h":{"InputFieldPath":"rain.3h","InputFieldLen":0,"OutputColName":"Rain_3Hour","OutputJsonColumnType":0},"rain.1h":{"InputFieldPath":"rain.1h","InputFieldLen":0,"OutputColName":"Rain_1Hour","OutputJsonColumnType":0},"snow.3h":{"InputFieldPath":"snow.3h","InputFieldLen":0,"OutputColName":"Snow_3Hour","OutputJsonColumnType":0},"snow.1h":{"InputFieldPath":"snow.1h","InputFieldLen":0,"OutputColName":"Snow_1Hour","OutputJsonColumnType":0}},"RootType":0,"JsonRootPath":"Result","InputColumnsToCopy":[]},"AdvancedSettings":{"CustomLocalTempDir":null}}


Thanks again,
Ivar
Apr 11, 2016 at 3:12 PM
Hi

So still struggling a bit with this. Now the package fails immediately when hitting the JSON plugin.

I have also attached the package with some printscreen and source if you feel like looking at it,
https://app.box.com/s/9ytmygf50yxobo8esffunhgemtasiksx
The copied file is the original, and the other one is the one I modified to include Array etc.

Error message is:
[JSON Source Component] Error: One or more errors occurred. at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Threading.Tasks.Task.Wait()
at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable
1 source, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Action3 bodyWithStateAndIndex, Func4 bodyWithStateAndLocal, Func5 bodyWithEverything, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable
1 source, ParallelOptions parallelOptions, Action1 body)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessArray(JArray arr, PipelineBuffer inputbuffer)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader sr, RootType rootType, PipelineBuffer inputbuffer, PipelineBuffer outputbuffer)Newtonsoft.Json.JsonException: Path returned multiple tokens.
at Newtonsoft.Json.Linq.JToken.SelectToken(String path, Boolean errorWhenNoMatch)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.<>c__DisplayClass17_1.<ProcessArray>b__0(IOMapEntry e)
at System.Threading.Tasks.Parallel.<>c__DisplayClassf
1.<ForWorker>b__c()
at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
at System.Threading.Tasks.Task.<>c__DisplayClass11.<ExecuteSelfReplicating>b__10(Object param0)


[JSON Source Component [61]] Error: System.Exception: Error occurred: One or more errors occurred. at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Threading.Tasks.Task.Wait()
at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Func4 bodyWithLocal, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable
1 source, ParallelOptions parallelOptions, Action1 body, Action2 bodyWithState, Action3 bodyWithStateAndIndex, Func4 bodyWithStateAndLocal, Func5 bodyWithEverything, Func1 localInit, Action1 localFinally)
at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable
1 source, ParallelOptions parallelOptions, Action1 body)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessArray(JArray arr, PipelineBuffer inputbuffer)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader sr, RootType rootType, PipelineBuffer inputbuffer, PipelineBuffer outputbuffer)Newtonsoft.Json.JsonException: Path returned multiple tokens.
at Newtonsoft.Json.Linq.JToken.SelectToken(String path, Boolean errorWhenNoMatch)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.<>c__DisplayClass17_1.<ProcessArray>b__0(IOMapEntry e)
at System.Threading.Tasks.Parallel.<>c__DisplayClassf
1.<ForWorker>b__c()
at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
at System.Threading.Tasks.Task.<>c__DisplayClass11.<ExecuteSelfReplicating>b__10(Object param0)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.ProcessInMemory(StreamReader sr, RootType rootType, PipelineBuffer inputbuffer, PipelineBuffer outputbuffer)
at com.webkingsoft.JSONSource_Common.JSONSourceComponent.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on JSON Source Component returned error code 0x80131500. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.


Sorry to keep bugging you with my troubles..
Coordinator
Apr 12, 2016 at 12:44 AM
Hi,

I've spent some time with your configuration. Thanks for all the details and files, they helped.
So the problem was easy to spot, but much more complicated to explain. JSON is a hierarchical language: it's very easy to build hierarchies with it. Tables are not. Converting a hierarchical object into a table is not trivial. So, when each JSON file is "mappable" to a single table (without any hierarchy), everything is straight forward. But your case is slightly different: you want to "keep" hierarchies for the following columns:
  • weather[:].main
  • weather[:].description
So, for each row, you might have mulitple weather objects. The component itself is not able in choosing one, so it complains about "multiple tokens found".
A possible improvement might be the row explosion: for each child element in the hierarchy copying the whole row. This however requires huge amount of memory.

Long story short, I've updated the binaries with another version that automatically flatten hierarchical objects into plain json stirngs, so you can later explode them via JsonFilter. I've taken the chance to improve the logging system and fire more accurate warning when needed.

Let me know if it's clearer and if you'll make it now.

Alberto.
Apr 12, 2016 at 10:18 AM
Hi,

Awesome, will give it a go.

Thanks a lot for helping.
Ivar
Apr 13, 2016 at 9:13 AM
Yeah... a bit lost again, probably need a step by step thing, or learn more about JSON..

Anyway the file slightly changed on me as well..

{"cnt":101,"list":[{"coord":{"lon":-2.1,"lat":57.14},"sys":{"type":1,"id":5119,"message":0.0315,"country":"GB","sunrise":1460437483,"sunset":1460488388},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"main":{"temp":282.15,"pressure":1011,"humidity":76,"temp_min":282.15,"temp_max":282.15},"visibility":10000,"wind":{"speed":5.7,"deg":80},"clouds":{"all":40},"dt":1460470800,"id":2657832,"name":"Aberdeen"},{"coord":{"lon":-1.28,"lat":51.67},"sys":{"type":1,"id":5159,"message":0.0409,"country":"GB","sunrise":1460437985,"sunset":1460487495},"weather":[{"id":520,"main":"Rain","description":"light intensity shower rain","icon":"09d"},{"id":721,"main":"Haze","description":"haze","icon":"50d"}],"main":{"temp":287.78,"pressure":1005,"humidity":71,"temp_min":285.15,"temp_max":290.15},"visibility":4800,"wind":{"speed":2.6,"deg":250},"clouds":{"all":1},"dt":1460470980,"id":2657780,"name":"Abingdon"},{"coord":{"lon":-2.36,"lat":53.75},"sys":{"type":1,"id":5060,"message":0.0312,"country":"GB","sunrise":1460438000,"sunset":1460487996},"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"},{"id":701,"main":"Mist","description":"mist","icon":"50d"}],"main":{"temp":282.75,"pressure":1006,"humidity":81,"temp_min":281.15,"temp_max":285.15},"visibility":10000,"wind":{"speed":2.1,"deg":360,"var_beg":320,"var_end":40},"clouds":{"all":40},"dt":1460472241,"id":2657770,"name":"Accrington"}]}

Problem that I ran into here is that there are like two main and descrition within the same array... wondering how to deal with that

Anyway, I feel I take up way too much of your time... so help if you like, if not I understand. :)
Coordinator
Apr 16, 2016 at 5:39 PM
Hi,

unfortunately I cannot help you more. Sorry about that, but I try to give precedence to other people that have problem installing this component or to develop new component features. Like you say, and as stated in the basic documentaiton, it is really a good idea to first learn json and jsonp, and later try to use this component.

Regards,
Alberto.
Apr 18, 2016 at 1:32 PM
Hi,

No problem, I do understand. Thank you for everything you assisted me with, very valuable and helpful.

As far as the plugin go, it is certainly a nice addition on the market. After playing with JSON for the last little while, I do think that your tool handles simple error free JSON files, nicely, but once it get a bit more complex with arrays within arrays it seems to struggle a bit. I may be wrong, and you may be able to do it in some way, but I was unable to do so with what I knew about your plugin and JSAN.. probably a combination of all.

Anyway good luck on your product.

Ivar
Marked as answer by webking on 4/27/2016 at 8:01 AM