用于编辑已存储在 SQL Server 中的 SSIS 配置的工具？

Question

有一个选项可以在 SQL Server 中保存 SSIS 配置。这似乎是个好主意。这样我的 SSIS 包不需要任何文件访问权限即可运行。

但它的存储格式对用户来说并不友好。是否有一种工具可以让您以友好的方式查看和编辑这些设置？

Answer 1

为了查看，我们总是只使用 SELECT *

我确实创建了一个过程，为我们的配置表导出特定的过滤器（我们称之为 sysdtsconfig），您还可以看到我们已经用我们的自己的元信息。

CREATE PROCEDURE
    [dbo].[ConfigurationExport]
(
    @TargetFilter VARCHAR (60)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE
        @DynamicStatement varchar(max)
    ,   @ConfigurationFilter nvarchar(150)
    ,   @ConfiguredValue nvarchar(255)
    ,   @PackagePath nvarchar(255)
    ,   @ConfiguredValueType nvarchar(20)
    ,   @Application nvarchar(50)
    ,   @Category nvarchar(50)
    ,   @Subcategory nvarchar(50)
    ,   @Comment nvarchar(255)

    -- set using statement
    SELECT
        @DynamicStatement = ''

    -- purge existing
    SELECT
        @DynamicStatement = @DynamicStatement + replicate('-', 80) + char(13) + char(10)
        + '-- Remove existing settings' + char(13) + char(10)
        + replicate('-', 80) + char(13) + char(10)

    SELECT
        @DynamicStatement = @DynamicStatement + 'DELETE C' + char(13) + char(10) +
    'FROM' + char(13) + char(10)
        + '    dbo.SYSDTSCONFIG C' + char(13) + char(10) + 'WHERE' + char(13) + char(10)
        + '    C ConfigurationFilter = ''' + @TargetFilter + '''' + char(13) + char(10)
    + char(13) + char(10)

    -- add current settings
    SELECT
        @DynamicStatement = @DynamicStatement + replicate('-', 80) + char(13) + char(10)
        + '-- Load values for ' + @TargetFilter + char(13) + char(10)
        + replicate('-', 80) + char(13) + char(10) + char(13) + char(10)

    DECLARE Csr cursor for
    SELECT
        ConfigurationFilter
    ,   ConfiguredValue
    ,   PackagePath
    ,   ConfiguredValueType
    ,   Application
    ,   Category
    ,   Subcategory
    ,   Comment
    FROM
        dbo.sysdtsconfig
    WHERE
        ConfigurationFilter = @TargetFilter


    SET NOCOUNT ON
    OPEN Csr
    FETCH NEXT FROM Csr INTO
        @ConfigurationFilter
    ,   @ConfiguredValue
    ,   @PackagePath
    ,   @ConfiguredValueType
    ,   @Application
    ,   @Category
    ,   @Subcategory
    ,   @Comment
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        SELECT
            @DynamicStatement = @DynamicStatement + 'INSERT INTO' + char(13) + char(10)
            + '    dbo.SYSDTSCONFIG' + char(13) + char(10)
            + '(' + char(13) + char(10)
            + '    ConfigurationFilter' + char(13) + char(10)
            + ',   ConfiguredValue' + char(13) + char(10)
            + ',   PackagePath' + char(13) + char(10)
            + ',   ConfiguredValueType' + char(13) + char(10)
            + ',   Application' + char(13) + char(10)
            + ',   Category' + char(13) + char(10)
            + ',   Subcategory' + char(13) + char(10)
            + ',   Comment' + char(13) + char(10)
            + ')' + char(13) + char(10)
            + 'SELECT' + char(13) + char(10)
            + '    ' + IsNull('''' + @ConfigurationFilter + '''', 'NULL')
            + ' AS ConfigurationFilter' + char(13) + char(10)
            + ',   ' + IsNull('''' + @ConfiguredValue + '''', 'NULL') + ' AS ConfiguredValue' + char(13) + char(10)
            + ',   ' + IsNull('''' + @PackagePath + '''', 'NULL') + ' AS PackagePath' + char(13) + char(10)
            + ',   ' + IsNull('''' + @ConfiguredValueType + '''', 'NULL') + ' AS ConfiguredValueType' + char(13) + char(10)
            + ',   ' + IsNull('''' + @Application + '''', 'NULL') + ' AS Application' + char(13) + char(10)
            + ',   ' + IsNull('''' + @Category + '''', 'NULL') + ' AS Category' + char(13) + char(10)
            + ',   ' + IsNull('''' + @Subcategory + '''', 'NULL') + ' AS Subcategory' + char(13) + char(10)
            + ',   ' + IsNull('''' + @Comment + '''', 'NULL') + ' AS Comment' + char(13) + char(10) + char(13) + char(10)

        END
        FETCH NEXT FROM Csr INTO
            @ConfigurationFilter
        ,   @ConfiguredValue
        ,   @PackagePath
        ,   @ConfiguredValueType
        ,   @Application
        ,   @Category
        ,   @Subcategory
        ,   @Comment
    END
    CLOSE Csr
    DEALLOCATE Csr

    IF (len(@dynamicStatement) > 256)
    BEGIN
        SELECT rtrim('-- Warning, text may be truncated!' + char(13) + char(10)
         + '-- Tools, Options, Query Results, SQL Server, Results to Text -> ' + char(13) + char(10)
         + '--     Set Maximum number of characters to 8192') 
        UNION ALL
        SELECT
            rtrim(@DynamicStatement)
    END
    ELSE
    BEGIN
        SELECT
            @DynamicStatement
    END
END

如果这不适合您，只需获取 Mladen 的 SSMS 工具包（免费）并使用它来编写您的脚本配置表。