Download JSON Data with SSIS


  1. Great Post Chris,

    I did an almost identical thing but my just keeps failing. I get a count on the (what for you would be features, which equals one(1)) but when I enter the foreach loop to add the rows it fails on AddRow.

    Object reference not set to an instance of an object.

    at ScriptMain.CreateNewOutputRows()
    at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap)
    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

    I found out the object is not getting serialized, but I still have no clue as to why.

    1. Author

      Glad you like the post. The first thing I’d check is if null values in your source data are nullable in your class. Class properties can be marked as nullable with a question mark as shown in the code below.

      public int? nullableInteger { get; set; }

    1. Author

      Thank you, Nancy. I have another blog post planned that uses SSIS to load JSON, but the heavy lifting is done with the new JSON functions in SQL Server 2016 instead of in SSIS. I prefer that technique, because it doesn’t require the tedious data type translations from C# to SSIS to SQL Server.

      1. Did you ever write this blog post? (use SSIS to load JSON but heavy lifting done in SQL Server). Couldn’t find it.

    1. Author

      Hey, John. Thank you for bringing this to my attention and linking to the Gist. I removed the link, because the Allegient site and blog are now gone due to the company being purchased by DMI.

  2. Amazing post. You saved me days which would have been wasted in trying to deploy newtonsoft.json in GAC of azure 🙂 :(. Keep it up. Amazing insight and elegant post

    1. Author

      Thank you very much for the feedback! I’m happy to hear that the information was helpful.

  3. Hi Chris! Nice and simple guide and well made!

    I’m not that experienced with C# yet, still learning and was wondering how you would go about in your script if the URL needs basic authentication?



  4. Great post! Thanks Chris!

    I’m really new to C# and was wondering how you would bake basic authentication in to your script?

  5. Can this method handle large JSON response from a restapi , say 100000+ records?
    I would prefer not using zippy or newtonsoft for bulk upload to SQLServer and hope that this solution can work for large JSON

  6. Hi Chris, I really like your article, I’m not great at coding but I can manage to follow the article and step through the code.
    However, I’m getting an error “code 499 message token required ” it looks like the json string returns nothing from the rest. Have you encountered this error before?

  7. For basic authentication…use Postman (or something similar) to determine what your headers need to look like for the API call to work correctly. For me, it was “Authorization: Token xyz123”. Then, add something like this:
    //set up the webclient
    using (WebClient client = new WebClient())
    //add the auth header
    client.Headers.Add(“Authorization”, “Token “);
    client.Headers[HttpRequestHeader.ContentType] = “application/json”;
    return client.DownloadString(downloadURL);

Leave a Reply (Markdown formatting available)

This site uses Akismet to reduce spam. Learn how your comment data is processed.