SSIS - Geometry Type
I have an XML Datasource that has a LAT\Long (Y,X) coordinate in it. I have written a script see below that converts those two columns into a SQL Geometry Objects. The conversion works perfectly. As specifed in the article I set the output column type of the script component task to Image for the geometry object because SSIS has not native geometry support. I found a article explaining that I need to change a xml file for SQL server so that SQL would know to interpret the image column as geometry, see configuration below. I have tried making these XML changes and I am still recieving the following error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error has occured. Error Code 0x80040E07. "Operand type clash: image is incompatible with geometry". I could really use some help on this. Thanks. 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); } } Simply copy this data type mapping and paste it on the same file and simply change the varbinary(max) in the comment to geometry. Aside from this, change the varbinarymax value in the dtm:DataTypeName for the dtm:SourceDataType to geometry. The new data type mapping for the geometry data type will look as follows: <!-- geometry --> <dtm:DataTypeMapping > <dtm:SourceDataType> <dtm:DataTypeName>geometry</dtm:DataTypeName> </dtm:SourceDataType> <dtm:DestinationDataType> <dtm:SimpleType> <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName> </dtm:SimpleType> </dtm:DestinationDataType> </dtm:DataTypeMapping>
August 27th, 2011 10:03am

If you are able to extract all the needed values from the XML to form SQL code like: INSERT INTO SpatialTable (GeomCol1) VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)); Why wouldn't you simply use Execute SQL Task to populate your database table (you will need to set the parameters properly up). Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2011 4:48pm

That is something I am implementing now. I would have liked to have used the native SSIS instead of having to put custom code in there but I was able to get it working. I created a Script Component and set is as the destination, I popultaed the parameters I need and did a manual insert. Will there be a better way to handle spatial types in SSIS in the future?
August 28th, 2011 7:35am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics