Achieve Spatial Data Support in SSIS
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.*/
public class ScriptMain : UserComponent
public override void PreExecute()
/* Add your code here for preprocessing or remove if not needed
public override void 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.
Pages: 1 2