CODEWITHSUNDEEP
jsonssiskingswaysoft
olf
olf

Reputation: 872

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?

Upvotes: 1

Views: 153

Answers (3)

olf
olf

Reputation: 872

And just another simple solution - even easier than the above!

enter image description here

I just took the first XXX Object and made the Output Type = Property Name As Column Value. Then I named it Output/Column Name indicator.

I also removed all the following Objects (which pointed to b, c etc) and voilá - it worked!

Upvotes: 0

olf
olf

Reputation: 872

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!

Upvotes: 0

KingswaySoft
KingswaySoft

Reputation: 927

This would be an SSIS limitation as the metadata is defined during the design time and cannot be changed dynamically during the runtime. This is also how the columns are generated initially, either by importing the document designer in the Document Designer Page or selecting the file in the Data Source Page. If the design is changed, including the node's name in the JSON component, you will receive an error, as it's looking for the previous document design. From the details provided, for this case, you would need to create a new dataflow for each different version of the metadata, with the variation to the node name.

Upvotes: 1

Related Questions