Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Spatial data Expand / Collapse
Author
Message
Posted Monday, August 9, 2010 8:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:54 AM
Points: 95, Visits: 453
Hi All

I 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 type
Post #965972
Posted Monday, August 9, 2010 10:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
Unfortunately there is no built-in support in SSIS for spatial data.

You will have to implement it yourself.

The following articles might be helpful:

http://www.sql-server-performance.com/articles/biz/spatial_data_support_ssis_p1.aspx
http://msdn.microsoft.com/en-us/magazine/dd434647.aspx

Post #966080
Posted Tuesday, August 10, 2010 1:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:54 AM
Points: 95, Visits: 453
Being 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
Post #966407
Posted Thursday, August 12, 2010 5:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
Ok, 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:
create table geo1 (id int, pt geometry)

The script looks like this:
/* 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);
}

}


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 luck


  Post Attachments 
SSIS_Spatial.zip (43 views, 21.17 KB)
Post #968077
Posted Friday, August 13, 2010 1:01 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:37 AM
Points: 4,848, Visits: 208
Sorry to jump in the middle of this but I needed somthing like this but thanks for the information.
Post #969210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse