June 23, 2020 at 12:00 am
Comments posted to this topic are about the item SSIS Variable and Parameter Analysis
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 23, 2020 at 10:38 pm
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.
June 23, 2020 at 10:56 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 23, 2020 at 11:18 pm
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.
June 23, 2020 at 11:25 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 24, 2020 at 5:05 pm
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!
June 26, 2020 at 5:35 pm
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
--__________________________________________________________________________________________________________________________________
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2020 at 11:01 pm
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!
July 10, 2020 at 1:27 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 10, 2020 at 9:58 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply