Insert XY table data into SQL Spatial column

Question

ArcGIS Server 10.1.1 SQL Server Standard 2012 Current workflow is user submits a table (text file) containing lat-long data (same spatial reference each time); SQL Server Integration services pushes this data into an SQL table. Data is viewed as an XY Event Layer in ArcGIS Desktop and published to AGS.

The ideal workflow is to to utilize a Query Layer instead of a XY Event Layer (according to ESRI).

How do I get these XY (Lat-Long) fields into the spatial column?

At this point in the workflow, I think we would be limited to SQL commands (or something within SSIS); the table is a text file going into SSIS so I doubt we'd be able to add an appropriately formatted column/field which would could then be mapped/loaded into the spatial column of the target SQL table.

Answer 1

Create a computed column using the Geometry type in SQL. The computed text in this case was:

([geometry]::STGeomFromText(((('POINT('+CONVERT([varchar](20),[Long]))+' ')+CONVERT([varchar](20),[Lat]))+')',(4326)))

Now the table can be added as a query layer within ArcMap.