SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Geometry Type


SSIS - Geometry Type

Author
Message
corcoranj.ctr
corcoranj.ctr
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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 dtmBigGrinataTypeName for the dtm:SourceDataType to geometry. The new data type mapping for the geometry data type will look as follows:

<!-- geometry -->
<dtmBigGrinataTypeMapping >
<dtm:SourceDataType>
<dtmBigGrinataTypeName>geometry</dtmBigGrinataTypeName>
</dtm:SourceDataType>
<dtmBigGrinestinationDataType>
<dtm:SimpleType>
<dtmBigGrinataTypeName>DT_IMAGE</dtmBigGrinataTypeName>
</dtm:SimpleType>
</dtmBigGrinestinationDataType>
</dtmBigGrinataTypeMapping>
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search