Cannot retrieve any data from within SSIS Data Flow Task

Topics: Bug / Problems
Jun 7, 2016 at 1:12 PM
Windows 7 Enterprise SP1 64-bit
Visual Studio 2010 v10.0.40219.1 SP1Rel
.NET 4.6.01055 SP1Rel

I have a flat file of data and I'm trying to use the JSON Source Component to connect to the file, parse and extract all of the data.

The data looks like:
Image

I have checked the data with JSON Lint - and it accepts it as a valid JSON file.

Image

My SSIS Data Flow components look like the following:

Image
Image
Image

When I click the Test button - I see:
![Image]https://i.imgsafe.org/6c2d549880.png

I only see one row of data - is this correct?

When I run the SSIS Data Flow Task - the Container runs and gets a green tick - but the Data Flow task never executes.

Image
Image

What am I doing wrong?
Coordinator
Jun 10, 2016 at 8:33 PM
Hi,

I've tried just now your exact configuration and it is working. Are you sure you are using the latest version of this software?
Image

Image

In case the problem persists, would you please provide your input file so I can test it right away and see where the problem is?

Alberto.
Jun 14, 2016 at 7:43 AM
I have re-downloaded the installer and run this. Not sure if I was supposed to uninstall the 'old' version first - but not sure how to. I have tried running Visual Studio 2012 Shell(Integrated) (v11.0.61219.0 Q11REL) as I saw that was what you used.

After re-running the installer - I created a new package from scratch and added the 3 components. Still can't get it to work.

I've uploaded my source package and data file (in a zip file): TEXT

How can I check what version I'm running?
Coordinator
Jun 14, 2016 at 7:57 AM

Hi,
Would you mind updating the entire project directory? Only the data package is not enough for me to debug.

Regards,

Alberto.

Jun 14, 2016 at 9:24 AM
Coordinator
Jun 16, 2016 at 7:41 AM
Sorry, still didn't have a chance to look at your package. I'll do it tonight. Stay tuned!
Jun 16, 2016 at 10:43 AM
It's interesting - I can get the JSON Filter to work - IF I parse and extract the grades data from the JSON file (it is a text file after all - but by then - I've done half the work. However, it's very messy manipulating the data into a form that JSON Filter requires).
Coordinator
Jun 16, 2016 at 1:05 PM
Can you explain better? I understood nothing.

Jun 17, 2016 at 7:51 AM
From the JSON text file:
  1. extract all the (array) data for the "grades" section
  2. strip all CR/LF characters, TABs etc
  3. clean up the data (remove the comma after each curly brace)
  4. add each entry into a string array
  5. pass each element of the array to JSONFilter
Essentially - I am decoding the JSON file to extract the "grades" data I'm interested in and doing a lot of manipulation to be able to present the data in the right format to the JSON filter component (this is essentially what the JSON Source Component does - admittedly probably a bit more than I've had to do so far - which is why I want to use the JSON Source Component).

As I can use the JSON FIlter successfully - I don't understand why I cannot get the JSON Source Component to work for me...After all - the libraries must be installed correctly for the Filter to work.
Coordinator
Jun 18, 2016 at 3:59 PM
Hi,

I've checked it. In my current configuration everything work as expected. However I've noticed a bug with the json filter references when using it alongside a SQL 2014 installation. In the latest release I've fixed it, but I'm not sure if your problem is about that. For me it is difficult to understand what is it, being hard to reproduce it on my configuration. Anyway, you should not use JsonFilter for your goal, use the JsonSource.

Anyways, on my system I had to update the package configuration, since I am running SQL2016 for testing reasons. Once I set TargetSqlVersion to SQL 2016 everything worked. Your package should work.
Jun 24, 2016 at 9:29 AM
I'm using SQL Server 2014 and Visual Studio 2010. If I use Studio 2012 - I see little difference. I'll download the latest version and retry.
Jun 28, 2016 at 1:24 PM
I've removed and re-installed everything a couple of times using the installer - and it now appears to work.

I can extract data out of a json file as long as the file doesn't start with an open square bracket. If it does - I can't find a Json response type that works. Starting with an open left brace is fine. Should it work?
Coordinator
Jun 28, 2016 at 1:28 PM
If the file starts with an open bracket, it means you have to interpret it as JSON array, not as a json object. To do that, simply change the value of the of combobox in JsonArray.

Image

Let me know if you figure it out,

Alberto
Jun 29, 2016 at 7:58 AM
How do I use JsonObject as a Json response type? I can't find the way to invoke it correctly.

The data file now looks like this (note: extra square braces):
Image

Attempt 1:
Image

Attempt 2:
Image
Coordinator
Jun 29, 2016 at 8:07 AM
Please read carefully the previous post: you MUST select JsonArray as Json response type combobox. You selected JsonObject instead.
Jun 29, 2016 at 8:45 AM
Apologies - looked at the image and selected Object...

Still can't find a combination that works at the moment. I've tried the following:

Image

Image

Image
Jun 29, 2016 at 9:06 AM
The thread "Deserialization of Json data" is most helpful here... I think this gives me the way forward
Coordinator
Jun 29, 2016 at 9:21 AM
Hi you are right. As you see, your new data strucute is now compoesed by an array (most outer) and and inner table (Grades is a table). For that reason, you should do exactly how described in that post (there is a link even in the documentation).

Anyways, if you don't mind matching every grade entry with its footer, you might try the following:

Path to json array => Empty
JSON Filed Name => grades[:].external_course_key

That should work fine, but you'll loose the matching between grades and footer.

Let me know if that helps!
Marked as answer by webking on 11/6/2016 at 4:40 PM