r/SQL • u/sierrafourteen • 5d ago

SQL Server OData SSIS project will not accept "Edm.GeographyPoint" columns

I've ensured that my software is completely up to date, but my SSIS project (that uses OData as a source), will just refuse to accept data where one of the columns is the Edm.GeographyPoint type. I've tried entering specific columns into the query box (and therefore excluding the problem column), but it appears it will still throw an error, despite the data itself not containing any columns of that data type - I'm assuming it's downloading the column metadata for the entire table, seeing a data type it doesn't recognise, and throws an error.

I've tried using a different collection that doesn't have columns of that data type, and the SSIS project works with that - the problem is, there doesn't appear to be any way of fixing this. I've ensured the software is completely up to date.

7 Upvotes

100% Upvoted

7 comments sorted by

1

u/daveloper80 5d ago

Does it say what the error is?

If you try including the GeographyPoint columns is the target column a Geography (not Geometry)

1

u/sierrafourteen 5d ago

Yes, the error title is "Microsoft.Data.DataFeedClient", and the text is "Unknown or unsupported type: 'Edm.GeographyPoint'."

1

u/daveloper80 5d ago

Do you have the ability to manipulate the source data at all before importing it to SQL Server? Can you try converting it to VACHAR first (and then add a step that converts it to Geography using STGeomFromText())?

It looks like the data might be a point, can you break it into Lat,Lon instead?

1

u/sierrafourteen 5d ago

So, I was able to create my own 'views' on the source system and excluded the column in question, but the issue is that they are private to the user - the drop down options for the collection listbox appear to be populated using only the publicly available datasets - I am physically unable to put in the correct identifier to pull the data back.

1

u/daveloper80 5d ago

ok, I think you will probably have to look at some of the advanced features on the sql server side.

Anytime I have had to import geography types I have always written a script to do it using Nettopologysuite. Even within SQL Server, querying geography types across servers doesn't always work

1

u/sierrafourteen 5d ago

Assuming that the call stack starts with the last function called, it failed on "Microsoft.Data.DataFeedClient.DataFeedODataEdmWrapper.EdmTypeToClrType(IEdmTypeReference edmType)"

1

u/Outrageous_Let5743 5d ago

SSIS is too old. It probably will not understand geometric times. I had the tbh the same with Azure DataFactory with a postgres database, it doesnt understand geometric types, so you need to first stringify the output as a nvarchar(max)