Forum Discussion

JoeO's avatar
JoeO
New Member
8 years ago

SSIS Connector Parse Json

Hi,

 

Im looking to integrate data from an external API to Dynamics CRM (on-prem). I'll probably end up with kingwaysoft. I just have a few quick questions.

 

  1. The body of the Json data that i retrieve (which i don’t control) is a bit weird, see example below. It basically sees the "null" as the root array and stops there. It doesn’t return the rest as i wish to pass it on to the next steps. I need to strip this "null" from the output. Is there an expression i could use to achieve this using SSIS? I was able to perform this in LogicApps/Flow using the "first(skip" expression. 
  2. Is the SSIS Scheduler the SQL Agent?
  3. Can i implement it to fire on specific triggers. For example, when a specific field in Dynamics changed (again, using kingwaysoft dynamics data flow)

 

Bad Json Example (What i receive)

[
null,
[
{
"user_id": 4200,
"user_label": "Test1"
},
{
"user_id": 2338,
"user_label": "Test2"
}
]
]

 

It should be like this:

[
{
"user_id": 4200,
"user_label": "Test1"
},
{
"user_id": 2338,
"user_label": "Test2"
}
]