Create SSIS Environment from script and give access script

/*****************************************************************************************************
* Name              : Script
* Description       : This script is to create envirnoment variables.
* Author   : Subrat Samal
*******************************************************************************************************
* Amendment History
*------------------------------------------------------------------------------------------------------
* ID Date             User Reason
*******************************************************************************************************
* 001 07/06/2018 Subrat Samal Initial version
******************************************************************************************************/
USE [SSISDB]

--Environment Folder - Project - Env Name
DECLARE @SubProjSSISFolder NVARCHAR(128) = N'SubProjSSIS'
DECLARE @SubProjSSISProject NVARCHAR(128) = N'BSS.SubProjSSIS'
DECLARE @SubProjSSISEnv NVARCHAR(128) = N'SubProjEnv'
------------------Environment variable---------------------------
DECLARE @EnvironmentVariablesData TABLE(EnvVariableName NVARCHAR(128) NOT NULL,EnvVariableParamValue NVARCHAR(1028) NOT NULL,EnvVariableType NVARCHAR(128) NOT NULL,ProjectVariableName NVARCHAR(128) NOT NULL,EnvVariableDescription NVARCHAR(1024) NOT NULL)
INSERT INTO @EnvironmentVariablesData
  (EnvVariableName ,EnvVariableParamValue ,EnvVariableType ,ProjectVariableName ,EnvVariableDescription) 
VALUES ('up_Par1' ,N'Value1' ,'String' ,'up_Par1' ,'Desc1'),
  ('up_Par2' ,N'Value2' ,'String' ,'up_Par2' ,'Desc1'),
  ('up_Par3' ,N'Value3' ,'String' ,'up_Par3' ,'Desc1'),
  ('up_Par4' ,N'Value4' ,'String' ,'up_Par4' ,'Desc1'),
  ('up_Par5' ,N'Value5' ,'String' ,'up_Par5' ,'Desc1')
------------------ Environment variable ---------------------------

----------------- Start -- Don't  change below code ---------------
DECLARE @Folder_Id BIGINT = (SELECT folder_id FROM [SSISDB].[catalog].folders f WHERE f.name = @SubProjSSISFolder)
DECLARE @Project_Id BIGINT = (SELECT folder_id FROM [SSISDB].[catalog].projects p WHERE p.folder_id = @Folder_Id)
DECLARE @Var_Object_Type INT = 20

-- Create the Folder
IF ISNULL(@Folder_Id,0) = 0
BEGIN
EXEC [SSISDB].[catalog].[create_folder] @Folder_Name = @SubProjSSISFolder, @Folder_Id = @Folder_Id OUTPUT
END

-- Give the folder a description
EXEC [SSISDB].[catalog].[set_folder_description] @Folder_Name = @SubProjSSISFolder, @folder_description = 'Folder containing SRS-SSIS routines'

-- Environment Id
DECLARE @Environment_Id BIGINT = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE [name] = @SubProjSSISEnv  AND [folder_id] = @Folder_Id)

--Drop existing Environment
IF ISNULL(@Environment_Id,0) > 0
BEGIN
EXEC [SSISDB].[catalog].[delete_environment] @environment_name = @SubProjSSISEnv, @Folder_Name = @SubProjSSISFolder
END

-- Create the environment
EXEC [SSISDB].[catalog].[create_environment] @environment_name = @SubProjSSISEnv, @environment_description = 'Environment configuration for SRS-SSIS', @Folder_Name = @SubProjSSISFolder

--Create environment variables with the values
WHILE((SELECT COUNT(*) FROM @EnvironmentVariablesData) > 0)
BEGIN
DECLARE @EnvVariableName AS NVARCHAR(128),@EnvVariableDescription AS NVARCHAR(128),@EnvVariableValue AS NVARCHAR(1028),@EnvVariableType AS NVARCHAR(128),@PrjVariableName AS NVARCHAR(128)
SELECT TOP 1 @EnvVariableName = [EnvVariableName],@EnvVariableDescription = [EnvVariableDescription],@EnvVariableValue = [EnvVariableParamValue],@EnvVariableType = [EnvVariableType],@PrjVariableName = [ProjectVariableName] FROM @EnvironmentVariablesData

IF NOT EXISTS (SELECT * FROM [SSISDB].[catalog].[environment_variables] WHERE name = @EnvVariableName AND [Environment_id] = @Environment_Id)
BEGIN
/* Create environment variable */
EXEC [SSISDB].[catalog].[create_environment_variable]
@variable_name = @EnvVariableName, 
@sensitive = False, 
@description = @EnvVariableDescription, 
@environment_name = @SubProjSSISEnv, 
@folder_name = @SubProjSSISFolder, 
@value = @EnvVariableValue, 
@data_type = @EnvVariableType

/* Map environment variable to SSIS parameteres */
EXEC [SSISDB].[catalog].[set_object_parameter_value]
@object_type = @Var_Object_Type, 
@parameter_name = @PrjVariableName, 
@object_name = @SubProjSSISProject, 
@folder_name = @SubProjSSISFolder, 
@project_name = @SubProjSSISProject, 
@value_type = R, 
@parameter_value = @EnvVariableName
END

DELETE FROM @EnvironmentVariablesData WHERE [EnvVariableName] = @EnvVariableName
END
--Create environment reference -- Associate Project with the Project Parameters created above
DECLARE @reference_id BIGINT
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[environment_references] r JOIN [SSISDB].[catalog].[projects] p ON [p].[project_id] = [r].[project_id]
 WHERE [p].[name] = @SubProjSSISProject AND [r].[environment_name] = @SubProjSSISEnv AND [p].[folder_id] = @Folder_Id)
BEGIN
EXEC [SSISDB].[catalog].[create_environment_reference] 
@environment_name = @SubProjSSISEnv
,@environment_folder_name = @SubProjSSISFolder
,@reference_id = @reference_id OUTPUT
,@project_name = @SubProjSSISProject
,@Folder_Name = @SubProjSSISFolder
,@reference_type = A
END
GO
----------------- End -- Don't  change above code ---------------



---------------------------------------------------------------------------------------------------
-- Assign [SSISDB] SSIS service account permissions
---------------------------------------------------------------------------------------------------
ALTER ROLE [ssis_admin] ADD MEMBER [BSS\svc-ACCOUNT]


----------------------------------------------Acceess rights to SSIS--------------------------------------------------------
-- Fetching Environment Id to Grant Read access for SRV account
DECLARE @EnvironmentIdAccess BIGINT
SELECT
TOP 1 @EnvironmentIdAccess = Env.environment_id 
FROM
SSISDB.[catalog].environments as Env
INNER JOIN
SSISDB.[catalog].folders as Fol
ON 
Fol.folder_id =Env.folder_id
INNER JOIN 
SSISDB.[catalog].projects as Prj
ON 
Prj.folder_id = Fol.folder_id
INNER JOIN
SSISDB.[catalog].environment_references as EnvRef
ON 
EnvRef.project_id = Prj.project_id
WHERE
Env.[name] = N'SubProjEnv' AND Fol.[name] = N'SubProjSSIS'

DECLARE @PrincipalId BIGINT
SELECT @PrincipalId = DATABASE_PRINCIPAL_ID(N'BSS\svc-ACCOUNT')

EXECUTE [SSISDB].[catalog].[grant_permission] 
@object_type=3,--Default object ID for SSIS Environment 
@object_id=@EnvironmentIdAccess,
@principal_id=@PrincipalId,
@permission_type=1 --Default value for Read Permission

----------------------------------------------------------------------------------------------

--Change owner
--exec sp_changedbowner 'sa'
ALTER AUTHORIZATION ON DATABASE::[$(DatabaseName)] TO [sa]
GO

-----------------------------------------------------------------------------------------------
--execute command line

DTExec /ISSERVER "\SSISDB\folderB\Integration Services Project17\Package.dtsx" /SERVER "." /Envreference 2 /Par "$Project::ProjectParameter(Int32)";1 /Par "Parameter(Int32)";21 /Par "Project::up_var(String)";"subrat" /Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb /Par "$ServerOption::SYNCHRONIZED(Boolean)";True


--------------------------------------------------------------------------
-- getting envreferecne id
--  Following PowerShell code into a .ps1 file to use it in your scripts.

Import-Module SQLServer Function Get-EnvironmentReferenceId (
[string]$EnvironmentName = "DEF", 
 [parameter(Mandatory = $true)][string]$ProjectPath) { If ($EnvironmentName -eq "DEF") 
# Get the default Environment for the current server environment { $EnvironmentName = [Environment]::GetEnvironmentVariable( 
"ENVIRONMENT_ID", "Machine") } $ProjectPathParts = $ProjectPath.Split("\") $ReferenceIdQuery = " SELECT er.reference_id FROM [internal].[folders] AS f JOIN [internal].[projects] AS p ON f.folder_id = p.folder_id JOIN [internal].[environment_references] AS er ON p.project_id = er.project_id WHERE f.name = '{0}' AND p.name = '{1}' AND er.environment_name = '{2}'" 
-f $ProjectPathParts[2], $ProjectPathParts[3], $EnvironmentName #$ReferenceIdQuery $QueryResults = Get-SqlData 
-SqlServer localhost 
-dbname SSISDB 
-qry $ReferenceIdQuery If ($QueryResults -eq $Null) { Throw New-Object System.ArgumentException 
"Environment '$EnvironmentName' is not configured for use with '$ProjectPath' on the this server.", 
"EnvironmentName" } Return $QueryResults[0] }

---------------------------------------------
Set-Location "C:\SSISScripts" CLS . .\GetEnvironmentReferenceId.ps1 # Determine which deployment model was used for this package # and set the DTExec arguments appropriately If ($PackagePath.SubString(0, 7) -eq "\SSISDB") { $EnvironmentReferenceId = Get-EnvironmentReferenceId $Environment $PackagePath $DTExecArgs = "/ISSERVER ""$PackagePath"" /Env $EnvironmentReferenceId /CHECKPOINTING OFF /REPORTING EW /Par """"`$ServerOption::SYNCHRONIZED(Boolean)"";True""" } Else { $DTExecArgs = "/DTS ""$PackagePath"" /CHECKPOINTING OFF /REPORTING EW" } Get-Location # Run DTExec $pinfo = New-Object System.Diagnostics.ProcessStartInfo If ($Architecture -eq "64") { $pinfo.FileName = "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" } Else { $pinfo.FileName = "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" } $pinfo.FileName # Output the DTExec path and filename $DTExecArgs # Output the DTExecArgs variable # The next few lines are required to make sure the process waits for # the package execution to finish $pinfo.RedirectStandardOutput = $true $pinfo.UseShellExecute = $false $pinfo.Arguments = $DTExecArgs $p = New-Object System.Diagnostics.Process $p.StartInfo = $pinfo $p.Start() | Out-Null $output = $p.StandardOutput.ReadToEnd() $p.WaitForExit() $DTExecExitCode = $p.ExitCode $output # DTExec Finished # This If/Else block is for our third-party scheduler that thinks # that negative return codes are equivalent to "success" If ($DTExecExitCode -ge 0) { $Result = $DTExecExitCode } Else { $Result = 0 - $DTExecExitCode } Write-Output "Return Code = $Result" Exit $Result