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>


