My project has 3 servers Dev for devlopment, Test for Testing and prod for production. I am asked to create SSISDB catalogs from scratch in all the 3 servers.
The folder is named ABC_Dev. Now I have to deploy the SSIS Project into folder named ABC_Devfolder using T-SQL Script. When the script runs on all 3 environments, the project and all the SSIS packages should be deployed into the catalog folder.
I tried something like this
DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK '<projectpath.ispac>', SINGLE_BLOB) as
BinaryData)
Exec catalog.deploy_project @folder_name = 'ABC_Dev', @project_name = 'ABC_Dev',
@Project_Stream = @ProjectBinary, @operation_id = @operation_id out
'<projectpath.ispac>' - I gave the path as
C:\Users\ABC_Dev\SSIS\ABC_Dev\bin\Development\ABC_Dev.ispac
Somehow its not taking that path on the servers.
Is there a way to declare the .ispac path that runs on all 3 servers when T-SQL script executes