SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / SSIS Spatial data / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 22:42:47 GMT20RE: SSIS Spatial datahttp://www.sqlservercentral.com/Forums/Topic965972-391-1.aspxSorry to jump in the middle of this but I needed somthing like this but thanks for the information.Fri, 13 Aug 2010 13:01:46 GMTRick.Cornell.SMERE: SSIS Spatial datahttp://www.sqlservercentral.com/Forums/Topic965972-391-1.aspxOk, some more help:I have created a sample SSIS package that can read data from a text file and write the data to a table containing a geometry column.The package contains a flat file source, a script transform, and a OLE DB destination.The flat file source extracts the Norting and Easting columns and feeds them into the script.The script adds a new column pt with datatype DT_IMAGE.My destination table is created using:[code="sql"]create table geo1 (id int, pt geometry)[/code]The script looks like this:[code="plain"]/* 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.Data.SqlTypes;using System.IO;[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]public class ScriptMain : UserComponent{ MemoryStream ms; BinaryWriter bw; public override void PreExecute() { base.PreExecute(); // Only allocate these once ms = new MemoryStream(10000); bw = new BinaryWriter(ms); } public override void Input0_ProcessInputRow(Input0Buffer Row) { // Create a SqlGeometry object representing the given data SqlGeometry g = SqlGeometry.STPointFromText(new SqlChars("POINT ("+Row.Northing+" "+Row.Easting+")"),0); // Serialize to a memory stream ms.SetLength(0); g.Write(bw); bw.Flush(); // Copy data from memory stream to output column with DT_IMAGE format Row.pt.AddBlobData(ms.GetBuffer(), (int)ms.Length); }}[/code]Note that you need to add a reference to Microsoft.SqlServer.Types to the script.In your application you might want to convert the northing and easting to geography (lat/long) but I have not done that to keep the sample simple.I have included a SSIS 2008 project that you can use directly if desired.Good luckThu, 12 Aug 2010 05:22:05 GMTStefan_GRE: SSIS Spatial datahttp://www.sqlservercentral.com/Forums/Topic965972-391-1.aspxBeing relatively new to SSIS 2008 could anybody help with transforming a text field to geography field, I have read the articles above and I still not sure (its also in c# and i am only have a bit of vb.net experience).I have 3 fields an identifier (1) and an easting coordinate(384405.00) and a northing coordinate(402283.00) and need them to be identifier and geography data type.A bit of help would be much appreciated:-DTue, 10 Aug 2010 01:49:54 GMTclucasiRE: SSIS Spatial datahttp://www.sqlservercentral.com/Forums/Topic965972-391-1.aspxUnfortunately there is no built-in support in SSIS for spatial data.You will have to implement it yourself.The following articles might be helpful: [url]http://www.sql-server-performance.com/articles/biz/spatial_data_support_ssis_p1.aspx[/url][url]http://msdn.microsoft.com/en-us/magazine/dd434647.aspx[/url]Mon, 09 Aug 2010 10:13:15 GMTStefan_GSSIS Spatial datahttp://www.sqlservercentral.com/Forums/Topic965972-391-1.aspxHi AllI have converted a SQL 2007 database to a SQL 2008 as I need to use spatial data.I have written a ssis task that reads data from a text file and writes the text to a sql2008 table, the problem i am having is that the longitude and latitude data is in a DT_WSTR format and i am not sure how to that into a geography data type as cannot see any any data types that match the geography data typeMon, 09 Aug 2010 08:04:49 GMTclucasi