使用BIML生成使用临时表的存储过程的SSIS包

Question

正如大多数人所知，当使用存储过程和临时表作为OleDbSource时，SSIS很难读取元数据。以前，可以通过在EXEC语句之前添加SET FMTONLY OFF;来轻松防止这种情况发生。不过，这样做的缺点是在验证期间执行了存储过程，这可能需要一段时间。从SQL 2012开始，我们可以使用WITH RESULT SETS指定列及其数据类型。SSIS将识别此内容，SQL领域中的所有问题都会解决。

不过，我想要生成一个使用此类存储过程作为源的BIML包，但我无法使其正常工作。假设我有一个名为'dbo.csp_MyCsp'的存储过程，它使用了一个名为'#MyTempTable'的临时表，其中包含1列'ColA int'。我正在尝试使用以下（类似的）Biml代码生成一个OleDbSource：

<OleDbSource ConnectionName="MyConnection" Name="OLE_SRC Test">
    <DirectInput>
        EXEC dbo.csp_MyCsp
        WITH RESULT SETS 
        (
            ([Col1] int)
        )
    </DirectInput>
</OleDbSource>

我遇到了一个错误，提示“#MyTempTable对象无效”。奇怪的是，如果我打开一个包并将该代码粘贴到我的OleDbSource中，它可以正常工作，没有任何错误。我有一种直觉，即SSIS和BIML的验证步骤不同。

你们有适当的解决方案吗？我不能使用FMTONLY OFF，因为存储过程需要一些时间来加载，这会导致生成超时。我正在使用SQL Server / SSIS 2014。

谢谢！ Marvin

- Marvin Schenkel

---

除了临时表，您尝试过其他方法吗？例如全局临时表或表变量？在暂存环境中，是否可以将临时表创建为常规表？ - iamdave

我已经考虑过了，但那不是真正的选项。我正在尝试加载一个（复杂的）事实表，需要一些中间步骤（临时表）才能执行良好。创建持久化临时表不符合架构，也不应该必要，因为奇怪的是，当我将查询粘贴到OLE_DB源中时，它可以工作，但是当我尝试生成它时就无法工作。我认为这是BIML验证问题。也许引擎仍在使用SSIS2008验证或类似的东西。从2012年开始，WITH RESULT SETS应该可以无缝运行。 - Marvin Schenkel

我在使用结果集语法调用带有CTE的存储过程时，将SET FMTONLY OFF添加到了其中。 - Doc

很遗憾，对我来说 SET FMTONLY OFF 不是一个选项，因为 BIML 生成将会因超时而失败。 - Marvin Schenkel

Answer 1

我以前也遇到过这些问题。我使用了这里描述的解决方案。原始答案并不是关于使用BIML生成，但我已经成功地在Visual Studio 2015中使用BIML Express与此解决方案。

我使用此存储过程作为示例：

CREATE PROCEDURE csp_MyCsp
AS
BEGIN

    SET NOCOUNT ON;

    IF 1 = 0
    BEGIN
        SELECT  CONVERT(INT, NULL) AS [database_id] 
        ,       CONVERT(SYSNAME, NULL) AS [name] 
    END;

    CREATE TABLE #mydatabases (
        [database_id] INT,
        [name] SYSNAME
    );

    INSERT INTO #mydatabases
    SELECT [database_id], [name]
    FROM sys.databases

    SELECT [database_id], [name]
    FROM #mydatabases

END;

以下是我的BIML代码中包含的内容：

EXEC dbo.csp_MyCsp WITH RESULT SETS (
    (
        [database_id] INT,
        [database_name] SYSNAME
    )
)

- JodyT

Answer 2

我在我的当前Biml项目中遇到了类似的问题。就像你提到的那样，问题似乎是Biml没有考虑存储过程内临时表的生成。

我的解决方案（变通方法？）是首先将它们创建为全局临时表，而不仅仅是临时表。然后，我创建了一个新的存储过程来运行与我的主存储过程相同的代码以处理临时表的创建。在Biml中运行“生成包”之前，我在SSMS中打开一个新的查询窗口，运行该存储过程并保持该窗口打开（全局临时表只要会话或此查询窗口打开即可持续）。

这有点麻烦，但同时，它将我的存储过程执行时间从35分钟缩短到5分钟，并且我只需要在“生成包”步骤中担心这个问题，所以我认为这是值得的。

- Brennahan