SQL导出SSIS包的内容到文件

[sql]代码库

USE [SSISDB]
GO
 
CREATE PROCEDURE [#spGetIspacFile]
    @project VARCHAR(255) ,
    @environmentFolder VARCHAR(50) ,
    @ispacTempFolder VARCHAR(100) ,
    @ispacFilePath VARCHAR(200) OUTPUT
AS
    BEGIN
        DECLARE @ispacFileName VARCHAR(200) = CONVERT(NVARCHAR(50), NEWID())
            + '.ispac'
 
        SET @ispacFilePath = @ispacTempFolder + '\' + @ispacFileName
 
 
        CREATE TABLE ##resultsTableVar
            (
              binaryProject VARBINARY(MAX)
            )
 
        INSERT  ##resultsTableVar
                ( binaryProject
                )
                EXEC [SSISDB].[catalog].[get_project] @folder_name = @environmentFolder,
                    @project_name = @project
 
        DECLARE @fileStream VARBINARY(MAX) ,
            @objectToken INT
 
        SELECT  @fileStream = binaryProject
        FROM    ##resultsTableVar
 
        EXEC sp_OACreate 'ADODB.Stream', @objectToken OUTPUT
        EXEC sp_OASetProperty @objectToken, 'Type', 1
        EXEC sp_OAMethod @objectToken, 'Open'
        EXEC sp_OAMethod @objectToken, 'Write', NULL, @fileStream
        EXEC sp_OAMethod @objectToken, 'SaveToFile', NULL, @ispacFilePath, 2
        EXEC sp_OAMethod @objectToken, 'Close'
        EXEC sp_OADestroy @objectToken
 
        DROP TABLE ##resultsTableVar
    END