How many times have you had to copy and paste the SSIS parameters from your project into the SSIS Catalog environment for a project? I said too many the last time we did a move to a new SSIS server.
Thanks to some help from online searches and Steve Ford on StackExchange for the last piece of xml processing, now it is a simple T-SQL script to generate the script used to add the environment and all parameters.
Sensitive parameters will still need the value updated. Below is the script.
/*
Copy SSIS project.params file to C:\Software of the SSIS server
Update @folder_name @environment @object_name @project_name
Run this script in SSMS connected to the SSIS server
Copy results to New Query window connected to SSIS server and run to create the environment
THE SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SCRIPT OR THE USE OR OTHER DEALINGS IN THE SCRIPT.
*/
use DBA
go
declare @sql varchar(max)
Declare @xmldata as xml
/*****************************************************************************
Check the folder name entered as parameter exists or fail procedure
******************************************************************************/
declare @environment sysname, @filepath varchar(max)
declare @object_name nvarchar(260),@folder_name nvarchar(128),@project_name nvarchar(128)
set @folder_name = ‘SSISTemplate’
set @environment = ‘Test’
set @object_name = ‘SSISTemplate’
set @project_name = ‘Template VS 17’
if @folder_name = (select name from ssisdb.internal.folders where name = @folder_name)
begin
/*****************************************************************************
Check the folder name entered as parameter does not already have an Environment by that name or fail procedure
******************************************************************************/
if (
SELECT count(e.name)
FROM [SSISDB].catalog.environments e
inner join [SSISDB].catalog.projects p
on p.folder_id = e.folder_id
inner join [SSISDB].catalog.folders f
on f.folder_id = e.folder_id
where e.name = @environment and f.name = @folder_name)<1
Begin
— SSIS project.params file
;WITH rs (xmldata) AS
(
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn
FROM OPENROWSET(BULK ‘C:\Software\Project.params’, SINGLE_BLOB) AS x
)
select @xmldata = (select * from rs)
;WITH XMLNAMESPACES (‘www.microsoft.com/SqlServer/SSIS’ AS ns)
,RS
AS (
SELECT @xmldata AS xmlData
)
SELECT Parm.value(‘@ns:Name’, ‘NVARCHAR(100)’) AS Parameter
,Id.value(‘.’, ‘NVARCHAR(100)’) AS Id
,CreationName.value(‘.’, ‘NVARCHAR(100)’) AS CreationName
,[Description].value(‘.’, ‘NVARCHAR(100)’) AS [Description]
,IncludeInDebugDump.value(‘.’, ‘NVARCHAR(100)’) AS IncludeInDebugDump
,[Required].value(‘.’, ‘NVARCHAR(100)’) AS [Required]
,Sensitive.value(‘.’, ‘NVARCHAR(100)’) AS Sensitive
,[Value].value(‘.’, ‘NVARCHAR(100)’) AS [Value]
,DataType.value(‘.’, ‘NVARCHAR(100)’) AS DataType
INTO #Params1
FROM RS
CROSS APPLY xmlData.nodes(‘/ns:Parameters/ns:Parameter’) AS T2(Parm)
OUTER APPLY T2.Parm.nodes(‘./ns:Properties’) AS T3(Props)
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”ID”]’) AS T4(Id)
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”CreationName”]’) AS T5(CreationName)
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”Description”]’) AS T6([Description])
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”IncludeInDebugDump”]’) AS T7([IncludeInDebugDump])
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”Required”]’) AS T8([Required])
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”Sensitive”]’) AS T9([Sensitive])
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”Value”]’) AS T10([Value])
OUTER APPLY T3.Props.nodes(‘./ns:Property[@ns:Name=”DataType”]’) AS T11([DataType])
select up.Parameter, up.Properties, up.[Values]
into #Params
from #Params1 pr
unpivot
(
[Values] for Properties IN (ID, CreationName, [Description], IncludeInDebugDump, [Required], Sensitive, [Value], DataType)
) AS up;
/*****************************************************************************
Create script using #params
******************************************************************************/
— DataType value conversion from project.params numeric to SSIS DataTypes
CREATE TABLE #DataType
(
ID INT,
Name SYSNAME,
Variable VARCHAR(10),
DataType VARCHAR(15));
INSERT INTO #DataType
VALUES
( 3, ‘Boolean’, ‘@Boolean’, ‘BIT’),
( 5, ‘SByte’, ‘@SByte’, ‘SMALLINT’),
( 6, ‘Byte’, ‘@Byte’, ‘TINYINT’),
( 7, ‘Int16’, ‘@Int16’, ‘SMALLINT’),
( 9, ‘Int32’, ‘@Int32’, ‘INT’),
( 10, ‘UInt32’, ‘@UInt32’, ‘BIGINT’),
( 11, ‘Int64’, ‘@Int64’, ‘BIGINT’),
( 12, ‘UInt64’, ‘@UInt64’, ‘BIGINT’),
( 13, ‘Single’, ‘@Single’, ‘FLOAT’),
( 14, ‘Double’, ‘@Double’, ‘FLOAT’),
( 15, ‘Decimal’, ‘@Decimal’, ‘DECIMAL(38, 18)’),
( 16, ‘Datetime’, ‘@Datetime’, ‘DATETIME’),
( 18, ‘String’, ‘@String’, ‘SQL_VARIANT’);
— Convert individual rows of Parameter Data into SQL statement code
select
Parameter,
Properties,
case
when Properties = ‘DataType’ then (select Name from #DataType d where ID = [Values])
when Properties = ‘Value’ then [Values]
end as DataType,
case
when Properties = ‘ID’ then ‘EXEC [SSISDB].[catalog].[create_environment_variable]’
when Properties = ‘CreationName’ then ‘@variable_name = N”’+Parameter+”’,’
when Properties = ‘Sensitive’ then ‘@sensitive =’+
case
when [Values] = ‘0’ then ‘FALSE’
when [Values] = ‘1’ then ‘TRUE’
END
+’,’
when Properties = ‘Description’ then ‘@description = N”’+
case
when [Values] is null then ”
when [Values] is not null then +[Values]
end
+”’,’
when Properties = ‘IncludeInDebugDump’ then ‘@environment_name = N”’+@environment+”’,’
when Properties = ‘Required’ then ‘@folder_name = N”’+@folder_name+”’,’
when Properties = ‘Value’ then ‘@value = ‘
when Properties = ‘DataType’ then ‘@data_type = N”’+(select Name from #DataType d where ID = [Values])+””
end as Statement
,[Values]
into #Script1
from #Params
— Compile Parameter, DataType and Property(value) into single row
select
a.Parameter, a.DataType, b.[values]
into #Script2
from #Script1 a
inner join #Script1 b
on a.Parameter = b.Parameter
where a.Properties = ‘DataType’ and b.Properties = ‘Value’
–Update rows with values from related rows within each parameter set
select
case
when left(s1.Statement,4) = ‘Exec’ then — Add variable value needed for @value input
case –Check for rows that include quotes enclosing the entire value
when left(s3.[Values],1) ='”‘ then ‘Set ‘+d.Variable+’ = N”’+left(right(s3.[Values],len(s3.[Values])-1),len(s3.[Values])-2)+””+s1.Statement
else ‘Set ‘+d.Variable+’ = N”’+s3.[Values]+”’; ‘+s1.Statement
end
when left(s1.Statement,6) = ‘@value’ then Statement + d.Variable+’,’ –Add correct variable name to @value statement
when left(s1.Statement,4) <> ‘Exec’ then Statement –return remaining rows unchanged
end Statement
into #Script3
from #Script2 s3
inner join #Script1 s1 on
s1.Parameter = s3.Parameter
left join #DataType d
on d.Name = s3.DataType
— create final result table
create table #Script4 (
IDCol INT IDENTITY(1,1),
Statement varchar(max))
— declare variables needed and create environment
insert into #Script4 (Statement)
select ‘Declare @Boolean BIT
, @SByte SMALLINT
, @Byte TINYINT
, @Int16 SMALLINT
, @Int32 INT
, @UInt32 BIGINT
, @Int64 BIGINT
, @UInt64 BIGINT
, @Single FLOAT
, @Double FLOAT
, @Decimal DECIMAL(38,18)
, @Datetime DATETIME
, @String SQL_VARIANT
, @folder_name nvarchar(128) = ”’+@folder_name+”+”’;’;
insert into #Script4 (Statement)
select ‘EXEC [SSISDB].[catalog].[create_environment] @environment_name = N”’+@environment+”’, @environment_description = N””’+’, @folder_name = ”’+@folder_name+”+”’;’;
insert into #Script4 (Statement)
select * from #Script3;
/*****************************************************************************
Create script to configure project to use parameters
******************************************************************************/
create table #configure(Statement varchar(max))
— add environment to project
insert into #configure
select
‘DECLARE @reference_id BIGINT; EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name = N”’
+@environment +””+’, ‘+’@reference_id = @reference_id OUTPUT, @project_name = N”’+@project_name +””+’, ‘
+’@folder_name = N”’+@folder_name+””+’, @reference_type = R;’
as Statement
— add parameters
insert into #configure
select DISTINCT
‘EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type = 20, @parameter_name = N”’+
Parameter +””+’, ‘+’@object_name = N”’+@object_name +””+’, ‘+’@folder_name = N”’+@folder_name +””+’, ‘
+’@project_name = N”’+@project_name +””+’, @value_type = R, @parameter_value = N”’+ Parameter +”’;’
as Statement
from #Params
insert into #Script4 (Statement)
select * from #configure
select Statement from #Script4
order by IDCol
/*****************************************************************************
Clean up
******************************************************************************/
drop table #configure
drop table #Params
drop table #Params1
–drop table #Params2
drop table #DataType
drop table #Script1
drop table #Script2
drop table #Script3
drop table #Script4
end
/*****************************************************************************
exit on failure
Check the folder name entered as parameter does not already have an Environment by that name
******************************************************************************/
else
begin
print ‘This Environment already exists for the Folder specified’
return
end
end
/*****************************************************************************
exit on failure
Check the folder name entered as parameter exists
******************************************************************************/
else
begin
print ‘The Folder specified does not exist’
return
end
0 Comments