Question 109876 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – What are the possibilities to call APIs in SSIS (Microsoft SQL Server Integration Services)?

I am looking for the different ways how within SSIS APIs can be called (e.g. getting data from a JSON file) or by calling APIs objects in SSIS can be accessed or configured (e.g. triggering an SSIS package).

Since I’m relatively new to SSIS and also have no hands-on experience with APIs in general, it’s a bit hard to figure out the topic for me. What I have already found out through research is:

  • You can call an API from your own code within a script task, but for that you need .NETor C# experience. (source)

  • There are third party tools like ZappySys, KingswaySoft or CozyRoc that provide so called Custom Tasks in SSIS, which are programmed by the third party themselves in .NET or C# (commercial).

Interfaces like APIs are one of the key requirements when it comes to data integration tools? It seems to me that SSIS is "lagging behind" here.

Doesn’t SSIS provide standardized tasks that allow the user to communicate with APIs like JSON, REST or Webhooks in a simple (no code) way?

Tags:

3

Answers


  1. 0

    I’m sorry to say, SSIS is lagging behind in terms of integration functionality. Most of features required in a modern platform are in Azure data factory.

    Your research of using a script component or 3rd party component is your only option.

    Login or Signup to reply.
  2. 0

    Using 3rd party tools like Zappysys fills this gap. It does exactly what you describe as your desire and it’s easier to use than most of the built-in components of SSIS. There are sevreral predefined connectors to most common services like amazon

    Login or Signup to reply.
  3. 0

    Disclaimer: I work for ZappySys

    Answer is NO. SSIS has no builtin API or JSON Processing components. In fact many ETL tools still fail to provide robust Universal API Connector. The main reason is simple, it’s not easy to write a connector which can read data from countless different APIs out there. Consider how many variations in protocols / authentication standards / pagination strategies / parser options and many more. Depending on which API you consuming you have to use different options (no silver bullet).

    Just to give you an idea on Pagination alone, its a challenging task. Check this one link to get an idea of how many types of pagination strategies are out there.

    Below are a few more reasons why SSIS team not adding new connectors themself and let 3rd party vendors like ZappySys fill the gap for modern data sources / standards.

    1. SSIS comes with SQL Server so any new features are usually shipped with SQL Server release cycle (usually every 2-3 years) so they may not add features that fast.
    2. SSIS offers a great extensibility framework so any vendor can write new components to fill the GAP so may be they are not too worried.
    3. Microsoft is very well aware that vendors like ZappySys can quickly fill the GAP at a very low cost, high performance, native look and feel and the same security features. This is one reason why they allow 3rd Party vendors to step in rather than build inhouse.
    4. Its a very challenging job to keep up with the features / fixes required to support so many modern data sources. Due to this smaller companies always have an advantage in frequent release cycles / speed / agility.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search