question

FrieseRalph-7806 avatar image
0 Votes"
FrieseRalph-7806 asked Monalv-msft edited

Unable to fetch data of a N:M relationship in OData webservice with SSIS 2016

Hi,

I want to store data from an OData web service (protocol V4) to a SQL Server 2016 database using SSIS 2016.
Accessing and saving entities (collections) works, for example "Employee" and "Territory". (see below)
However, I do not know how the N:M relationship ("NavigationProperty") between "Employee" and "Territory" can be queried and stored.
One employee could be assigned to none/one or many territories and vice versa.

Is this possible in one query?
Or do I have to program a loop over all IDs of "Employee" and query the "Territories" to this ID?
How do I configure the OData Source Editor in both cases?

web service:
https://services.odata.org/V4/Northwind/Northwind.svc/

desired data:
https://services.odata.org/V4/Northwind/Northwind.svc/Employees?$select=EmployeeID,Territories&$expand=Territories($select=TerritoryID)
When I enter this query in the OData Source Editor, I only get a list of all EmployeeIDs. (see the picture)

destination table:
tEmployeeTerritory (EmployeeID, TerritoryID)

Part of the data model (edm) of the webservice:
<EntityType Name="Employee">
<Key>
<PropertyRef Name="EmployeeID"/>
</Key>
<Property xmlns:p5="http://schemas.microsoft.com/ado/2009/02/edm/annotation" Name="EmployeeID" Type="Edm.Int32" Nullable="false" p5:StoreGeneratedPattern="Identity"/>
...
<NavigationProperty Name="Territories" Type="Collection(NorthwindModel.Territory)" Partner="Employees"/>
</EntityType>


<EntityType Name="Territory">
<Key>
<PropertyRef Name="TerritoryID"/>
</Key>
<Property Name="TerritoryID" Type="Edm.String" Nullable="false" MaxLength="20"/>
<Property Name="TerritoryDescription" Type="Edm.String" Nullable="false" MaxLength="50"/>
<Property Name="RegionID" Type="Edm.Int32" Nullable="false"/>
<NavigationProperty Name="Region" Type="NorthwindModel.Region" Nullable="false" Partner="Territories">
<ReferentialConstraint Property="RegionID" ReferencedProperty="RegionID"/>
</NavigationProperty>
<NavigationProperty Name="Employees" Type="Collection(NorthwindModel.Employee)" Partner="Territories"/>
</EntityType>


82778-northwindv4nmrelationship.png


sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FrieseRalph-7806 avatar image
0 Votes"
FrieseRalph-7806 answered Monalv-msft commented

Hi Mona,

Thank you very much for your answer.
Yes, there is no Territory column in the Employee object because the relationship between Employee and Territory is N:M, not 1:N.
In the latter case, the Employee object might contain a foreign key column ("TerritoryID").
Or the Territory object, the foreign key column "EmployeeID".
Depending on which reality is to be depicted.

Retrieving The OData data from objects is not a problem.
Retrieving The OData data from N:M relationships between objects does not seem to be that easy.

The links mentioned are all known and very helpfull.
Yesterday I examined the third link again in more detail.
This time I had a hit with the settings.

In the picture you can see the settings to get all TerritoryIDs of the employee with id = 2.
Unfortunately, only the data of one employee and not the data of all the employees in a single query.

This solution creates many more problems.
For example the field "Resource path" must be set dynamically to loop to queries all territorys of all employees.
A "parameter" can be defined for the field.
However, handling "prameters" instead of "variables" seems cumbersome in SSIS.
Perhaps there is still an easy way to go.

See links:
https://www.timmitchell.net/post/2020/07/02/ssis-parameters/
https://www.youtube.com/watch?v=3HcPJCBS_KY

Best regards
Ralph


83267-northwindv4empterritory.png



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @FrieseRalph-7806 ,

Could you please the screen shot of the way you used the parameter?

Best regards,
Mona

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @FrieseRalph-7806 ,

According to your web service, it seems that there's no Territories column.

Please refer to the following links and pictures:
1.OData Source
2.Tutorial: Using the OData Source
3.Using the OData Source in SQL Server Integration Services

83238-odataconnectionmanager.png
83239-odatasource.png

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.