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

SQL Server - Loop through a table variable in TSQL without using a cursor

DECLARE @Rowcount INT
SELECT @Rowcount = COUNT(*) FROM [dbo].[AddressTable];

WHILE( @Rowcount > 0)
BEGIN
SELECT @Rowcount = @Rowcount - 1;
SELECT [AddressId]
       ,[Name]
       ,[Address]
FROM [dbo].[AddressTable]
ORDER BY [AddressId] DESC
OFFSET @Rowcount ROWS
FETCH NEXT 1 ROWS ONLY;
END
----------------------------------------------------
    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @ProductID INT
    DECLARE @ProductName VARCHAR(100)
    DECLARE @TotalQty INT
    DECLARE @Total MONEY
    DECLARE @Index INT
    DECLARE @RecordCnt INT


    DECLARE @ProdID TABLE(
     iSNo INT IDENTITY(1,1)
    ,iProductID INT
    )

    DECLARE @ProductSales TABLE
    (
        iSNo INT IDENTITY(1,1)
       ,iProductID INT
       ,vProductName VARCHAR(100)
       ,iTotalQty INT
       ,iGrandTotal MONEY
    )


    SELECT @Index = 1

     INSERT INTO @ProdID(iProductID)
    SELECT ProductID FROM Products ORDER BY ProductID ASC

    SELECT @RecordCnt = COUNT(iSNo) FROM @ProdID


    WHILE (@Index <= @RecordCnt)
    BEGIN
       SELECT @ProductID = iProductID FROM @ProdID WHERE iSNo = @Index
       SELECT @ProductName = ProductName FROM Products
              WHERE ProductID = @ProductID
       SELECT @TotalQty = SUM(Quantity),@Total = SUM(UnitPrice*Quantity) FROM "Order Details" WHERE ProductID = @ProductID
       INSERT INTO  @ProductSales(iProductID,vProductName,iTotalQty,iGrandTotal)
              VALUES(@ProductID,@ProductName,@TotalQty,@Total)
       SELECT @Index = @Index + 1
    END
     SELECT * FROM @ProductSales
END
GO
----------------------------------------------------

--Audit Entry - Starts
DECLARE @AuditRowcount INT
DECLARE @UserName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @OldData NVARCHAR(MAX)
DECLARE @NewData NVARCHAR(MAX)
SELECT @AuditRowcount = COUNT(*) FROM @TblAuditHistory;

WHILE( @AuditRowcount > 0)
BEGIN
        SELECT @AuditRowcount = @AuditRowcount - 1;
SELECT @UserName = [UserName]
  ,@TableName = [TableName]
  ,@OldData = [OldData]
  ,@NewData = [NewData]
FROM @TblAuditHistory
ORDER BY [Date] DESC
OFFSET @AuditRowcount ROWS
FETCH NEXT 1 ROWS ONLY;
EXEC [Audit].[usp_InitiateAuditService] @UserName,@TableName,@OldData,@NewData;
END
--Audit Entry - Ends
----------------------------------------------------------

--Create another temp table with identity column CREATE TABLE #EmployeeCopy (LoopId INT IDENTITY(1,1),   Id INT, Name NVARCHAR(100), Status TINYINT) --Copy data to the table with identity column INSERT INTO #EmployeeCopy(Id, Name, Status) SELECT Id, Name, Status FROM #Employee   DECLARE @LoopCounter INT , @MaxEmployeeId INT,         @EmployeeName NVARCHAR(100) SELECT @LoopCounter = min(LoopId),@MaxEmployeeId = max(LoopId) FROM #EmployeeCopy WHILE ( @LoopCounter IS NOT NULL         AND  @LoopCounter <= @MaxEmployeeId) BEGIN    SELECT @EmployeeName = Name    FROM #EmployeeCopy  WHERE LoopId = @LoopCounter    PRINT @EmployeeName     SELECT @LoopCounter  = min(LoopId)    FROM #EmployeeCopy  WHERE LoopId > @LoopCounter END -------------------------------------------------------