使用SQL Server 2008 SSIS数据流通过带有数据源参数的过程从SQL Server 2005中检索数据

I was trying to retrieve data from a procedure on SQL Server 2005 and populate a table on SQL Server 2008. Packet is on SQL Server 2008. I have a SQL Task which I use to get the date. The result set is set to a variable called MeasurementPeriodid, an int32 data type. In my data flow in an OLEDB source I have the SQL command: exec [dbo].[MIS_usp_Core_Fill_Arranegement] ?. On the Parameters tab, @MSR_PRD_ID is mapped to User::MeasurementPeriodid.

我试图从SQL Server 2005上的过程中检索数据并在SQL Server 2008上填充表。数据包在SQL Server 2008上。我有一个SQL任务,我用它来获取日期。结果集设置为名为MeasurementPeriodid的变量,即int32数据类型。在我的OLEDB源数据流中,我有SQL命令:exec [dbo]。[MIS_usp_Core_Fill_Arranegement] ?.在“参数”选项卡上,@ MSR_PRD_ID映射到User :: MeasurementPeriodid。

The procedure accepts MSR_PRD_ID INT. In the procedure the result set is returned from table variable. Columns in this table, which correspond to columns in destination (and which is date data type), are a datetime data type.

该过程接受MSR_PRD_ID INT。在该过程中,结果集从表变量返回。此表中的列(对应于目标中的列(以及日期数据类型))是日期时间数据类型。

When I tried to preview I get this error:

当我尝试预览时,我收到此错误:

Error at Arrangement [OLE DB Source 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: "Conversion failed when converting datetime from character string.".

排列错误[OLE DB源1]:SSIS错误代码DTS_E_OLEDBERROR。发生OLE DB错误。错误代码:0x80004005。 OLE DB记录可用。来源:“Microsoft SQL Server Native Client 10.0”Hresult:0x80004005说明:“从字符串转换日期时转换失败。”。

Error at Arrangement [OLE DB Source 1]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

排列错误[OLE DB源1]:无法从数据源检索列信息。确保数据库中的目标表可用。

(Microsoft Visual Studio)

(Microsoft Visual Studio)

When I execute the procedure from SSMS it works fine. I tried to comment out datetime columns in the query which returns a result set from table variable in the procedure, but the error is the same.

当我从SSMS执行该过程时,它工作正常。我试图在查询中注释掉datetime列,它从过程中的表变量返回结果集,但错误是相同的。

Procedure (not all, just begining and end):

程序(不是全部,只是开始和结束):

ALTER PROCEDURE [dbo].[MIS_usp_Core_Fill_Arranegement] 
@MSR_PRD_ID INT
AS
/**
TestSQL:
EXEC [dbo].[MIS_usp_Core_Fill_Arranegement] @MSR_PRD_ID = 20160831

**/
BEGIN
DECLARE @lclval nvarchar(20),       
    @datum varchar(15),
    @imalacid int

SET NOCOUNT ON;
SET FMTONLY OFF;

DECLARE  @Arrangement  TABLE
(
    [MeasurementPeriodId] datetime,
    [ArrangementId] bigint,
    [ArrangementUniqueId] nvarchar(100),
    [CustomerId] bigint,
    [CustomerUniqueId] nvarchar(100),
    [ArrangementProductId] int, -- MAPIRANJE
    [OrganizationUnitId] bigint,
    [OrganizationUnitUniqueId] [nvarchar](100),
    [NonPerformingLoanFlag] bit,
    [ActivationDate] datetime,
    [ArrangementTypeUniqueId] nvarchar(100),
    [LastRenewalDate] datetime,
    [NumberOfIssues] int, -- BROJ IZDATIH KARTICA
    [ExpirationDate] datetime, -- EST_END_DT
    [CancelationDate] datetime, -- END_DT
    [CurrencyId] int,
    [CurrencyTypeId] int,
    [TermTypeId] int
)

select --[MeasurementPeriodId],
    [ArrangementId],
    [ArrangementUniqueId],
    [CustomerId],
    [CustomerUniqueId],
    [ArrangementProductId], -- MAPIRANJE
    [OrganizationUnitId],
    [OrganizationUnitUniqueId],
    [NonPerformingLoanFlag],
    --[ActivationDate],
    [ArrangementTypeUniqueId],
    --[LastRenewalDate],
    [NumberOfIssues], -- BROJ IZDATIH KARTICA
    --[ExpirationDate], -- EST_END_DT
    --[CancelationDate], -- END_DT
    [CurrencyId],
    [CurrencyTypeId],
    [TermTypeId]
from @Arrangement

1 个解决方案

#1

If you modify your stored procedure to add the following no-op right after the BEGIN, does SSIS play nicely with the stored procedure?

如果修改存储过程以在BEGIN之后添加以下no-op,SSIS是否可以很好地与存储过程一起使用?

IF (NULL = NULL)
BEGIN
    SELECT
        CAST(NULL AS datetime) AS [MeasurementPeriodId]
    ,   CAST(NULL AS bigint) AS [ArrangementId]
    ,   CAST(NULL AS nvarchar(100)) AS [ArrangementUniqueId]
    ,   CAST(NULL AS bigint) AS [CustomerId]
    ,   CAST(NULL AS nvarchar(100)) AS [CustomerUniqueId]
    ,   CAST(NULL AS int) AS [ArrangementProductId] 
    ,   CAST(NULL AS bigint) AS [OrganizationUnitId]
    ,   CAST(NULL AS [nvarchar](100)) AS [OrganizationUnitUniqueId]
    ,   CAST(NULL AS bit) AS [NonPerformingLoanFlag]
    ,   CAST(NULL AS datetime) AS [ActivationDate]
    ,   CAST(NULL AS nvarchar(100)) AS [ArrangementTypeUniqueId]
    ,   CAST(NULL AS datetime) AS [LastRenewalDate]
    ,   CAST(NULL AS int) AS [NumberOfIssues] 
    ,   CAST(NULL AS datetime) AS [ExpirationDate]
    ,   CAST(NULL AS datetime) AS [CancelationDate]
    ,   CAST(NULL AS int) AS [CurrencyId]
    ,   CAST(NULL AS int) AS [CurrencyTypeId]
    ,   CAST(NULL AS int) AS [TermTypeId];
END

If so, the root issue is that SSIS is having trouble determining the structure of the output table because it's a temporary object. The above hack is the 2005/2008 way of providing a hint to the SSIS engine what the metadata will look like.

如果是这样,根本问题是SSIS在确定输出表的结构时遇到问题,因为它是一个临时对象。上述黑客攻击是2005/2008为SSIS引擎提供元数据外观的提示。