SSIS Variable and Parameter Analysis

  • Phil Parkin

    SSC Guru

    Points: 244655

    Comments posted to this topic are about the item SSIS Variable and Parameter Analysis

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Misha_SQL

    SSCertifiable

    Points: 5397

    Thank you for the article and sharing your code and research. While working on it, have you encountered solutions, which do similar things for the packages stored in SSISDB?  It should be easier since everything is already in the database tables.

  • Phil Parkin

    SSC Guru

    Points: 244655

    Misha_SQL wrote:

    Thank you for the article and sharing your code and research. While working on it, have you encountered solutions, which do similar things for the packages stored in SSISDB?  It should be easier since everything is already in the database tables.

    Thank you.

    You would think that querying SSISDB would be easier, but it isn't. Package data in SSISDB is stored in an encrypted format. While it may be possible to adapt my query to include decryption (and assume everyone remembers their master key password!), I chose not to go down that route for the sake of simplicity.

    I also consider this a good development practice – any tidying up of variables and parameters should be performed before deployment rather than after, and that is another reason for doing this at the file level.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Misha_SQL

    SSCertifiable

    Points: 5397

    Thank you! We do all of our deployments via SSISB catalog (that's why I asked the question). I thought this was now the Microsoft-recommended way of doing things. We do have many environments, so doing things via catalog works well for us. I am curious why you prefer doing it via files instead.

  • Phil Parkin

    SSC Guru

    Points: 244655

    Misha_SQL wrote:

    Thank you! We do all of our deployments via SSISB catalog (that's why I asked the question). I thought this was now the Microsoft-recommended way of doing things. We do have many environments, so doing things via catalog works well for us. I am curious why you prefer doing it via files instead.

    I always deploy to SSISDB.

    But all my development is performed in Visual Studio, using files in the file system and strict version control.

    In my opinion, catching any issues at development time, before deploying to SSISDB, is better than deploying, fixing and then deploying again.

    My code does not consider environments. It looks only at packages, parameters and connection managers.

    I may write another article at some stage which analyses environments, projects and variable mappings, which may be of interest to you.

     

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Misha_SQL

    SSCertifiable

    Points: 5397

    Phil Parkin wrote:

    Misha_SQL wrote:

    Thank you! We do all of our deployments via SSISB catalog (that's why I asked the question). I thought this was now the Microsoft-recommended way of doing things. We do have many environments, so doing things via catalog works well for us. I am curious why you prefer doing it via files instead.

    I always deploy to SSISDB.

    But all my development is performed in Visual Studio, using files in the file system and strict version control.

    In my opinion, catching any issues at development time, before deploying to SSISDB, is better than deploying, fixing and then deploying again.

    My code does not consider environments. It looks only at packages, parameters and connection managers.

    I may write another article at some stage which analyses environments, projects and variable mappings, which may be of interest to you.

    Makes sense. Thank you again!

  • Phil Parkin

    SSC Guru

    Points: 244655

    Misha, I have written some code which analyses SSISDB environment variables and their mappings to SSISDB parameters. Would you be able to run it and let me know whether it is useful? (Also, whether it contains any bugs!!) Thank you.

    USE SSISDB;
    GO

    SET NOCOUNT ON;

    --__________________________________________________________________________________________________________________________________
    --#region Create and populate a temp table containing all environment variables
    DROP TABLE IF EXISTS #EnvVars;

    CREATE TABLE #EnvVars
    (
    EnvironmentName NVARCHAR(128) NOT NULL
    ,FolderName NVARCHAR(128) NOT NULL
    ,VariableName NVARCHAR(128) NOT NULL
    ,VariableDescription NVARCHAR(1024) NULL
    ,VariableType NVARCHAR(128) NOT NULL
    ,IsSensitive BIT NOT NULL
    ,VariableValue SQL_VARIANT NULL
    ,
    PRIMARY KEY CLUSTERED (
    EnvironmentName
    ,FolderName
    ,VariableName
    )
    );

    INSERT #EnvVars
    (
    EnvironmentName
    ,FolderName
    ,VariableName
    ,VariableDescription
    ,VariableType
    ,IsSensitive
    ,VariableValue
    )
    SELECT EnvironmentName = e.name
    ,FolderName = f.name
    ,VariableName = ev.name
    ,VariableDescription = ev.description
    ,VariableType = ev.type
    ,IsSensitive = ev.sensitive
    ,VariableValue = ev.value
    FROM catalog.environment_variables ev
    JOIN catalog.environments e
    ON e.environment_id = ev.environment_id
    JOIN catalog.folders f
    ON f.folder_id = e.folder_id
    ORDER BY e.name
    ,ev.name;

    --#endregion Create and populate a temp table containing all environment variables
    --__________________________________________________________________________________________________________________________________
    --#region Create and populate a temp table containing parameter-variable mapping details
    DROP TABLE IF EXISTS #Mappings;

    CREATE TABLE #Mappings
    (
    ProjectName NVARCHAR(128)
    ,ParameterScope VARCHAR(7)
    ,PackageName NVARCHAR(260)
    ,ParameterName NVARCHAR(128)
    ,MappedEnvironmentVariableName NVARCHAR(128)
    ,MappedEnvironmentVariableValue SQL_VARIANT
    ,IsSensitive BIT
    ,EnvironmentFolder NVARCHAR(128)
    ,EnvironmentName NVARCHAR(128)
    );

    INSERT #Mappings
    (
    ProjectName
    ,ParameterScope
    ,PackageName
    ,ParameterName
    ,MappedEnvironmentVariableName
    ,MappedEnvironmentVariableValue
    ,IsSensitive
    ,EnvironmentFolder
    ,EnvironmentName
    )
    SELECT ProjectName = p.name
    ,ParameterScope = IIF(p.name = op.object_name, 'Project', 'Package')
    ,PackageName = IIF(p.name = op.object_name, NULL, op.object_name)
    ,ParameterName = op.parameter_name
    ,MappedEnvironmentVariableName = ev.name
    ,MappedEnvironmentVariableValue = ev.value
    ,IsSensitive = ev.sensitive
    ,EnvironmentFolder = f.name
    ,EnvironmentName = e.name
    FROM catalog.object_parameters op
    JOIN catalog.environment_references er
    ON er.project_id = op.project_id
    JOIN catalog.projects p
    ON p.project_id = op.project_id
    JOIN catalog.environments e
    ON e.name = er.environment_name
    JOIN catalog.folders f
    ON f.folder_id = e.folder_id
    JOIN catalog.environment_variables ev
    ON ev.environment_id = e.environment_id
    WHERE op.referenced_variable_name IS NOT NULL
    AND er.environment_folder_name = f.name
    AND op.referenced_variable_name = ev.name;

    --#endregion Create and populate a temp table containing parameter-variable mapping details
    --__________________________________________________________________________________________________________________________________
    --#region Return results
    --Unmapped environment variables
    SELECT Comment = 'Unmapped SSISDB Environment Variable'
    ,*
    FROM #EnvVars ev
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM #Mappings m
    WHERE m.EnvironmentName = ev.EnvironmentName
    AND m.EnvironmentFolder = ev.FolderName
    AND ev.VariableName = m.MappedEnvironmentVariableName
    );

    --Mappings
    SELECT Comment = 'Mapped Environment Variable'
    ,*
    FROM #Mappings m;
    --#endregion Return results
    --__________________________________________________________________________________________________________________________________

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Misha_SQL

    SSCertifiable

    Points: 5397

    Phil, thank you very much for taking the time to write these queries and I apologize for the delay in replying (works gets in the way of my fun 🙂 ).

    I tried using your queries and they worked great with one correction. I had to comment out this line in the WHERE clause:

    -- AND er.environment_folder_name = f.name

    We don't have environment folders so er.environment_folder_name  is always NULL.  Outside of that, I think it does the job. I will post more if I run into anything else.

    Thank you again!

    • This reply was modified 3 weeks, 6 days ago by  Misha_SQL.
  • Phil Parkin

    SSC Guru

    Points: 244655

    Misha_SQL wrote:

    Phil, thank you very much for taking the time to write these queries and I apologize for the delay in replying (works gets in the way of my fun 🙂 ).

    I tried using your queries and they worked great with one correction. I had to comment out this line in the WHERE clause:

    -- AND er.environment_folder_name = f.name

    We don't have environment folders so er.environment_folder_name  is always NULL.  Outside of that, I think it does the job. I will post more if I run into anything else.

    Thank you again!

    Misha, thanks for getting back to me. I did wonder about the possibility of NULL environment folders, though I was never sure why anyone would choose that option. Do you do it to allow for relative paths? I'll add it to the to-do list.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Misha_SQL

    SSCertifiable

    Points: 5397

    I don't have a good reason for not having environment folders. I think it was just on oversight on our part.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply