Using SQL Server spatial types in SSIS data load

Question

Hi there,

I am trying to load a file using the SQL Server Import Data function - appending rows to an existing table.

my existing table is created like this:

CREATE TABLE [dbo].[load](
    [Long] [varchar](50) NULL,
    [Lat] [varchar](50) NULL,
    [Geog]  AS ([geography]::STGeomFromText(((('POINT('+[Long])+' ')+[Lat])+')',(4326)))
)

however when the load tries to execute i see the following error messages. I believe the issue is around the computed column 'Geog' as the data loads fine when i take this out of the table definition.

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "While reading current row from host, a premature end-of-message was encountered--an incoming data stream was interrupted when the server expected to see more data. The host program may have terminated. Ensure that you are using a supported client application programming interface (API).". (SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (47)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (47)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - load_school" (34) failed with error code 0xC0209029 while processing input "Destination Input" (47). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

any assistance or pointers would be greatly appreciated.

Answer 1

So it would appear that having a computed column which contains the geography type in the definition is not supported (i tried with other types including the POINT type and it worked fine). This could be down to the lack of support that SSIS has for the spatial data types or down to the way that SQL Server processes the computed column during a batch insert.

Either way i had to create a solution to my issue and came up with the following work around.

In my SSIS package i start by removing the computed column which is causing the issues. Then i run the data load process Then i re-add the computed column to the table using the ALTER statement.

This works fine for the time being, although it could cause some issues with off-line data during a run-time scenario. however, it should be simple enough to address this issue.

This did mean that i had to create a more complex SSIS package than just using the simple 'Import data' wizard provided by SQL Server, and i still feel a little frustrated that i dont know the actual cause of my issue. I just found a way to work around it and move on. I would still appreciate if someone could point out why i was seeing this failure.