在SSIS数据加载中使用SQL Server空间类型 [英] Using SQL Server spatial types in SSIS data load

问题描述

我正在尝试使用SQL Server导入数据功能加载文件-将行追加到现有表中。

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

但是，当负载尝试执行时我看到以下错误消息。我认为问题出在计算列'Geog'周围，因为当我从表定义中删除数据时，数据会很好地加载。

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.

错误0xc0202009：数据流任务1：
SSIS错误代码DTS_E_OLEDBERROR。发生
OLE DB错误。错误代码：
0x80004005。 OLE DB记录可用
。来源： Microsoft SQL
服务器本机客户端10.0结果：
0x80004005说明：虽然
从主机读取当前行，但
的消息过早为$ b $遇到b-当服务器
希望看到更多数据时，传入的数据流
被中断。主机
程序可能已终止。确保
您使用的是受支持的客户端
应用程序编程接口
（API）。。 （SQL Server导入和
导出向导）错误0xc0209029：
数据流任务1：SSIS错误代码
DTS_E_INDUCEDTRANSFORMFAILUREONERROR。
输入目标输入（47）
失败，因为发生了错误代码0xC020907B
，并且在输入目标
输入上放置了错误行
（47）指定因
错误而失败。指定的
组件的
指定的对象发生错误。在此之前可能会发布错误
消息，其中包含有关失败的更多
信息。 （SQL
服务器导入和导出向导）
错误0xc0047022：数据流任务1：
SSIS错误代码
DTS_E_PROCESSINPUTFAILED。处理输入目标
输入（47）时，组件
目标-load_school（34）
上的
ProcessInput方法失败，错误代码为0xC0209029
。标识的组件
从
ProcessInput方法返回错误。该错误是特定于组件的
，但
错误是致命的，将导致Data
Flow任务停止运行。
可能会在此
之前发布错误消息，并提供有关
故障的更多信息。 （SQL Server导入和
导出向导）

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)

推荐答案

不支持在定义中包含包含地理类型的计算列（我尝试使用其他类型（包括POINT类型），但效果很好）。这可能归因于SSIS对空间数据类型缺乏支持，或者归因于SQL Server在批处理插入期间处理计算列的方式。

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.

在我的SSIS程序包中，我首先删除引起问题的计算列。
然后我运行数据加载过程
然后我使用ALTER语句将计算列重新添加到表中。

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.

这确实意味着我不得不创建一个比使用简单的导入数据向导更复杂的SSIS程序包。由SQL Server提供，但我仍然感到有点沮丧，因为我不知道问题的真正原因。我只是找到了解决它并继续前进的方法。如果有人能指出我为什么会遇到这种失败，我仍然感激不尽。

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.

这篇关于在SSIS数据加载中使用SQL Server空间类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！