Package Design and Development:
Follow the below steps to test the
above concept.
1) Create a new package,
and provide a relevant name.
2) Add a new data flow task, edit it and add a new OLE DB Source Adapter. Edit
this and add a query as shown in the below screenshot. “Location”
is the spatial datatype field in our dataset. We are using the “AsGml” function
which is an instance level function on spatial datatypes. This function exports
data as GML and the return type is XML if queried from SQL Server. But this
will still map to DT_IMAGE, so cast it to a large sized NVARCHAR.
Also ensure this is the mapping using the Advanced Editor dialog. We also need the
Srid and we will use the STSrid property of the geography datatype.
Keep in mind that these function names are case-sensitive and this query
can be implemented at the database level as well.
3) Add two dervied columns named “Lat” and “Long” of unicode string datatype
with a length of 30-40 characters.
4) Add a Script transform and ensure that the Location and SRID input columns
are READ-ONLY, and that Lat and Long input columns are READ-WRITE.
5) Ensure that the script language is C# (since we are coding the script in C#
for this demonstration) then click on Edit Script to go to the script editor
environment. Add Reference to the Microsoft.SqlServer.Types assembly, add the below
code and then build the script:
/* Microsoft SQL
Server Integration Services Script Component
* Write scripts using Microsoft Visual
C# 2008.
* ScriptMain is the entry point class
of the script.*/
using
System;
using
System.Data;
using
Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using
Microsoft.SqlServer.Dts.Runtime.Wrapper;
using
Microsoft.SqlServer.Types;
using
System.Xml;
using
System.IO;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public
class ScriptMain
: UserComponent
{
public override
void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override
void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override
void Input0_ProcessInputRow(Input0Buffer Row)
{
SqlGeography geo =
SqlGeography.GeomFromGml(new System.Data.SqlTypes.SqlXml(XmlReader.Create(new StringReader(Row.location))),
Row.SRID);
Row.Lat = geo.Lat.ToString();
Row.Long = geo.Long.ToString();
}
}
First we are declaring that we are using Micorosft.SqlServer.Types namespace for
the
SqlGeography class, System.Xml namespace for the XmlReader class and the System.IO
namespace for the StringReader class.
Then in the ProcessInputRow method we are creating an object of SqlGeography
class using the GML that we captured from the “Location” spatial field. This
value is passed as the first argument of “GeomFromGml” which is a static method
of the SqlGeography class. Also we pass the SRID that we extracted from the same as
the second argument of the GeomFromGml method.
Once the object is created, we have access to all the .NET CLR functions on this
object that we would have in the query environment of SQL Server. Thereafter we
assign the value of Lat (Latitude) and Long (Longitude) properties to the
derived columns, in order to test that we can extract information from the
spatial value stored in the “Location” field.
6) Now add a Multicast transformation to use it as a destination. Add a
Dataviewer between Script and Multicast transform and you should be able to find
the result similar to the below screenshot.
Summary: Based on the above exercise we can conclude that, by using .NET
CLR assemblies that contains classes which map to spatial datatypes, we can
achieve the same level of support for spatial datatypes in SSIS that
we have in a SQL Server query environment.
]]>