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

Question

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

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

当我尝试预览时，我得到了以下错误：

安排错误[OLE DB源1]：SSIS错误代码DTS_E_OLEDBERROR。发生了OLE DB错误。错误代码: 0x80004005。OLE DB记录是可用的。来源：“10.0”H结果: 0x80004005描述：“从字符串转换日期时间时转换失败。” 排列错误[OLE DB source 1]：无法从数据源检索列信息。确保数据库中的目标表可用。

(Microsoft Visual Studio)

当我从SSMS执行这个过程时，它工作得很好。我试图注释掉查询中的datetime列，该列从过程中的表变量返回结果集，但错误是相同的。

​

​

程序(并非全部，只是开始和结束)：

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

Answer 1

如果您修改存储过程以在BEGIN之后添加以下no，那么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

如果是这样的话，根本问题是SSIS无法确定输出表的结构，因为它是一个临时对象。上述黑客是2005/2008年度向SSIS引擎提供提示的方式，元数据将是什么样的。