Achieve Spatial Data Support in SSIS

SQL Server 2008 introduced a new category of datatypes known as
spatial datatypes which store spatial information. The new spatial datatypes are
geography and geometry. SQL Server Management Studio comes with
good good support
for these spatial data types like the spatial results tab and facilitated access
to .NET Common Language Runtime (CLR) functions on these datatypes.

SQL Server Integration Services (SSIS) is one of the business intelligence
applications which is most  sensitive to datatypes. Even in the R2 version
of SQL Server 2008, it can be safely said that there is no real out-of-box
support for these spatial data types. The only possible datatype the maps in
SSIS to any spatial datatype is DT_IMAGE, which means that any spatial data is
treated like a binary large object (blob) data. There may  be cases when
spatial data from two different database servers are brought in and one may
wish to perform some spatial functions on the these. However ,  if
the data is  DT_IMAGE, it is almost impossible to dissect the
different pieces of information from this data.

Solution :
There is no built-in support for spatial data types in
SSIS, which means that there is no datatype which exactly matches the spatial
datatype and when any spatial field is read using a source adapter, the
datatype that will be mapped to any spatial field is  DT_IMAGE. The
solution to this issue is re-building the datatype in SSIS. Script
transformation is the only component for rebuilding any spatial datatype,
and  we will also examine the reason for this.

this demonstration, we will consider a dataset
which has any spatial datatype field, and to keep our focus on the concept, we
consider the geography datatype. We will read this datatype from a SQL Server table
and then extract  information from this datatype. This exercise will
demonstrate how to use spatial datatypes in SSIS to the same level as in SQL Server using SSMS.

Environmental setup:
this exercise, will use a table consisting of at least
one field of the geography datatype having at least one record. To save time and
effort, I will reuse the Address table that we built in the
BI-Satellite project,
as below:

Key Factors in the Design Decision:
Before we start with the package
design, there are several points to keep in mind :

1) Every geography datatype has a spatial reference identifier (Srid) associated
with it, which will be stored along with the value stored in the field.

2) To rebuild a .NET CLR datatype (i.e. geography) we need to use a transform
that is capable of facilitating access to .NET assemblies, which is the Script

3) Almost all of the spatial static or instance .NET CLR functions capable of
creating a spatial object take two input parameters: i) Srid and ii)
geometrical structure in text, xml, binary or a byte stream format.

4) Microsoft.SQLServer.Types namespace contains classes related to spatial
datatypes, and for the geography datatype the class that we require is SqlGeography.

5) Geography Markup Language (GML) is one of the XML based data format standard
defined by Open Geospatial Consortium (OGC), and fortunately the geography
has built-in functions to export GML data.

6) We need to make sure that when we read data into the package,
this should
be read in some form of text and not as a DT_IMAGE datatype. If the data is read
as DT_IMAGE, then it will be  impossible to cast it to a datatype which
can be fed to a .NET CLR function to  create a spatial datatype.


Pages: 1 2


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |