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 Errro 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>

Viewing 0 posts

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