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.*/
public class ScriptMain : UserComponent
public override void 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
// Copy data from memory stream to output column with DT_IMAGE format
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.