Search code examples
jsonssiskingswaysoft

SSIS Kingswaysoft Json Source Dynamic Node name

I am an experienced SSIS-developer, but brand new to Kingswaysoft Json Source Editor (and all other Kingswaysoft SSIS-stuff!)

I have several hundreds API sources which are build very similar:

{
 "values": {
  "XXX": {
    "a": {
      "2000": -0.26,
      "2001": -0.146,
      "2002": -0.112,
      "2003": -0.131,
      "2004": -0.042,
      "2005": -0.126,
      "2006": -0.085,
      "2007": -0.061
    },
    "b": {
      "2000": 1.26,
      "2001": 1.146,
      "2002": 1.112,
      "2003": 1.131,
      "2004": 1.042,
      "2005": 1.126,
      "2006": 1.085,
      "2007": 1.061
    }
   }
  }

The only difference is the XXX (line 3 - called Indicators) which is unique in each api's.

I know how I can loop all the Indicators in one SSIS-package and now I want to call another package with the Indicator as a parameter.

As I see it, I need this value, as my Node Name apparently must be the same in the Document Designer:

Kingswaysoft Document Designer

Is there a way where I can dynamically change the XXX value in the Document Designer? Or is it possible somehow to add a "catch-all" name?

Solution

  • Thank you to KingswaySoft for reaching out. And you gave me another idea which works in order to solve this specific case!

    I have changed my flow a little: First I have added a variable: json_txt (type string). Then on the Control Flow, I have added a HTTP Requester Task, which simply loads my json into my json_txt variable. I am so lucky, that my indicator is also the Relative Path, so I can use an Expression to set the Relative Path = my Indicator.

    Then entering a Data Flow: I start with a simple SQL selection (SELECT 1 AS dummy) as I need some type of source. This is really just ignored afterwards! Then I add a derived column, where i add the column json and it is derived from this formula:

    (DT_NTEXT)REPLACE(@[User::json_txt], @[$Package::Indicator] ,"input")

    After my Derived column, I use the JSON Extract which can take a column as input. I point it to my json column. And now I know that the Indicator has been replaced and is always called input!

    Control Flow

    The Control Flow

    Data Flow

    The Data Flow

    Works like a charm!