4) Edit the script of this script transform, and add the code as shown in the
below screenshot in the area bordered by the red box. As we know that Script
task will not allow use of user variables except post execute event, so we
created a local variable at the class level. Then we create the command
text for updating each row, and finally just before the end of script execution
when the postexecute event is fired, we assign this command string value
to the user variable we created at the package level.
STGeomFromText is a static function of Geography data type, so we have used the
syntax of geography::STGeomFromText which can be perceived as
“class.function()”. It expects a “Point” spatial data object with geographical
latitudes and longitudes as input values, which we have used as an input
parameter of STGeomFromText function. We already have the latitude and
longitude, and SRID for Well Known Text (WKT) is referenced by a constant value
of “4326”.
There are other static functions which can be used for the same purpose, but the
above function is the most generic one that can accomodate any kind of spatial
data object such as Point, Linestring, Polygon etc. For those interested in
learning more on on WKT and Spatial Data Objects there is a good explanation on each of these topics available on MSDN BOL.
After adding the code, build the script.
5) Switch back to Control Flow, and add an “Execute SQL Task” after the Data
Flow task. Configure this task to use its command text from the user variable
we created in step 2.
6) If the modifications have been done as above, the package should
execute successfully and update the values in the “Location” field of address
table as shown in the below screenshot.
Data Analysis
Open SQL Server Management Studio (SSMS), and select all the records from
the
“Address” table. You should see three tabs: Results, Spatial Results
and Messages. Spatial Results is a new tab available in SSMS
2008, which is a part of spatial data support. Browse to this tab, and you
should see a figure and a pane on your right hand side.
SSMS is intelligent enough to detect the only spatial column in our resultset,
and has selected it in the spatial column selection. There are four different
types of projection available for viewing data of the resultset on the
projection. I have selected “Bonne” projection due to it’s resemblance to the
world map. After selecting this you will see dots
on the diagram which are the points we created on the geography.
Hover your mouse over any specific point to see the data in the
tooltip as shown in the below screenshot. We had 12 records in our original
dataset, and in the below screenshot I have marked out all these points. You can also
experiment with different spatial data objects such as polygons and linestrings to see how
the output could be projected differently
Summary:
Continuing from the first part of this article series, we
modified the Address table and the package. Firstly we modified Address table to
include a field named “Location” of geography data type. Then we modified our
SSIS package to create a point spatial data object using latitudes and
longitudes as input values and update the location field with the same. Finally
we took a quick view of spatial data support in SSMS by browsing this data
visually in the spatial results tab.
After the above exercise, you should be able to answer three questions: What is
the
geography data type, how can spatial data objects be created and assigned to a
field of the geography data type, and what kind of support is available for spatial
data in SSMS 2008.
]]>