SSIS Spatial data

Question

Post reply

SSIS Spatial data

clucasi

SSCrazy

Points: 2113
More actions
August 9, 2010 at 8:04 am

#225261

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

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

clucasi SSCrazy Points: 2113 More actions · Answer 2

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:-D

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

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

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 4

Sorry to jump in the middle of this but I needed somthing like this but thanks for the information.