Import a SSIS Project From Catalog Using T-SQL Script
In SSIS 2012 we have project deployment model available where SSIS projects can be deployed to Integration Services Catalog. There are many scenarios where we need to import a project back to our local environment and do some manipulations with them. One straightforward way of doing it is by using SQL management studio by connecting to the required SQL Server instance and through object explorer import project option available from within the Integration Service catalogs. There was recently a question asked in the forums asking whether there's any way of achieving the same using T-SQL script.
This article explains about a T-SQL script which you can use to import a SSIS project from the Integration Services Catalog.
The SSISDB has a system procedure available under catalog schema called get_project which can be utilized for this purpose. The get_project will accept as arguments the name of the folder and project within the IS catalog and returns the project in file_stream.
We will make use of a FileTable for getting the stream data accessed as a file through the file system. You should be on SQL 2012 or above for using the FileTable feature.
A detailed explanation on how to configure and setup the FileTable feature at the server and database level and how you can do file manipulations using it can be seen from the below link
Working with FileTables
The code will look like below for extracting and loading the project information to a FileTable
DECLARE @t table
(
v varbinary(max)
)
INSERT @t
EXEC SSISDB.catalog.get_project 'Training','Integration Services Project1'
INSERT [dbo].[MyFileTable] (name,file_stream)
SELECT 'Test.ispac',v
FROM @t
This will cause returned file_stream to be saved as a .ispac file in the directory to which FileTable points. You can confirm this by right clicking on the FileTable from the object explorer and choosing the option Explore FileTable Directory.
You can then click on the ispac file and deploy the project to the Integration Services Catalog of the local server instance using the IS deployment wizard which gets launched.
Now what if the attempt is to get individual package out of project and do some manipulation?
For that we would need to have one more step included in our script For getting the individual files out we need to first get the file saved in any of the compressed formats like zip,rar etc.
Then the script would need to be modified as below
DECLARE @t table
(
v varbinary(max)
)
INSERT @t
EXEC SSISDB.catalog.get_project 'Training','Integration Services Project1'
INSERT [dbo].[MyFileTable] (name,file_stream)
SELECT 'Test.zip',v
FROM @t
This would cause the file stream to get saved in zip format which you can extract using any of the unzipping softwares like winzip, winrar ,7zip etc
If you want to do this from sql script you can invoke it using xp_cmdshell extended procedure call.
In my case I used Winrar and so script will look like this
Exec xp_cmdshell '"<full path>\Winrar\winrar.exe" x <FileTableDirectoryPath>\Test.zip <NewFolderpath>'
If its winzip it will look like below
EXEC xp_cmdshell'"<path>/winzip32.exe" -e <FileTableDirectory>\Test.zip <NewFilePath>'
This would cause individual files to be extracted to the specified folder and you will see all dtsx files which were a part of the project inside the folder as seen below
From this we would be able to use any of the individual packages for further manipulation
We can convert this logic to a generic procedure script as below which can be used for importing the project or the individual packages from Integration Services Catalog in case we need to do any further manipulation using them
The procedure would look like below
CREATE PROCEDURE ImportSSISCatalogProject
@FolderName varchar(100), --Folder where project resides in SSIS catalog
@ProjectName varchar(100), --name of project in the catalog
@OutputFileName varchar(100), --Name of the output file
@ExtractFiles bit = 0, -- do we require project or extract individual files out of it
@ApplicationPath varchar(200) = NULL, --Path of application used for uncompressing the files
@OutputFolderPath varchar(200) = NULL -- path to which files have to be extracted
AS
DECLARE @t table
(
v varbinary(max)
)
SELECT @OutputFileName = @OutputFileName + CASE WHEN @ExtractFiles = 0 THEN + '.ispac' ELSE '.zip' END
--get the file_stream data for the project
INSERT @t
EXEC SSISDB.catalog.get_project @FolderName,@ProjectName
--Avoid conflict with existing files
UPDATE [dbo].[MyFileTable]
SET name = REPLACE(@OutputFileName,'.','_old.')
WHERE name = @OutputFileName
--Load the file data to FileTable
INSERT [dbo].[MyFileTable] (name,file_stream)
SELECT @OutputFileName,v
FROM @t
--Get directory path for the FileTable file which we saved
DECLARE @FilePath varchar(1000)
SELECT @FilePath = FileTableRootPath() + file_stream.GetFileNamespacePath() FROM [dbo].[MyFileTable]
WHERE name = @OutputFileName
IF @ExtractFiles = 1
BEGIN
DECLARE @UnzipCmd varchar(1000)
SET @UnzipCmd = '"'+ @ApplicationPath +'" x ' + @FilePath + ' ' + @OutputFolderPath
PRINT @UnzipCmd
--Do the extraction of the files
EXEC xp_cmdshell @UnzipCmd
END
GO
This procedure can be invoked as below for getting IS project imported as a whole (.ispac file)
EXEC ImportSSISCatalogProject '<Folder Name>','<Project Name>','<Project Filename>'
To extract the individual .dtsx files out use it like below
EXEC ImportSSISCatalogProject '<Folder Name>','<Project Name>','<Zip Filename>',1,'<application path name>','<output folder path>'
Further references on related topics can be seen from the below link
get_project procedure
get_project documentation
winzip commandline documentation
winzip commandline