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 - Geometry Type Expand / Collapse
Author
Message
Posted Wednesday, August 10, 2011 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 8:40 AM
Points: 2, Visits: 26
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>
Post #1157623
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse