I was fortunate to attend a training on SSIS by my good friend and SSIS expert Andy Leonard(b|t). We already use the tool for ETL purposes where I work and I have mid level familiarity with it. I was able to pick up many best practices and tips and tricks. I highly recommend Andy’s training for those who want to get familiar with SSIS.
I will be blogging on a few things I learn on my journey to SSIS expertise. This is the first one. This came about as a result of wanting to delete a few packages from ssis catalog. We do not use these packages any more, and I wanted to clean them out from the project which resides on a few servers. I looked into a few ways of doing it.
1 Using SSIS to delete the packages from the project, and then re-deploy the project without the said packages on to the servers. This is probably the simplest way to do it.
2 Delete the entire project from ssis catalog and re deploy it. This is not possible when you have some packages that are in use, particularly in production.
Both of these methods are discussed another SSIS expert Tim Mitchell(t) ’s blog post here.
I found a third method that was a bit more elegant and easier to use for deployment – particularly in situations where the SSIS developer has limited access to production. This method uses varbinary project stream that is generated from the catalog.
1 I have an SSIS project with 3 packages as below.
2 I deploy this project to my development server from SSDT.
4 Now, I delete the packages I do not need – package1.dtsx and package2.dtsx. My project looks like below.
5 5 I repeat step 2 and redeploy the project to SSIS catalog on my development server. This project does not have the packages I do not need. I can push the same to production using scripting as below.
6 I generate the varbinary stream for this project using below script on development server, where it is already deployed.
USE SSISDB
GO
DECLARE @ProjectStream TABLE (Stream varbinary(max));
INSERT INTO @ProjectStream
EXECUTE SSISDB.[catalog].[get_project]
@folder_name = N'LearningSSIS',
@project_name = N'Integration Services Project1';
SELECT CAST('0x' + CONVERT(varchar(max), stream, 2) as XML)
FROM @ProjectStream;
7 I can cut and paste this into my deployment script as below:
USE SSISDB
GO
DECLARE @folder_name nvarchar(128) = ' LearningSSIS',
@folder_id bigint = NULL,
@project_name nvarchar(128) = 'Integration Services Project 1’, @project_stream VARBINARY(max) = xxxxxxxx,
@operation_id bigint = NULL,
@message nvarchar(max);
--Create the folder if it doesn't exist
IF NOT EXISTS
(SELECT 1 FROM catalog.folders AS f WHERE f.name = @folder_name )
BEGIN
PRINT N'Create folder for ' + @folder_name
EXECUTE [catalog].[create_folder]
@folder_name = @folder_name,
@folder_id = @folder_id OUTPUT;
END;
PRINT N'Deploy project ' + @project_name
EXECUTE [catalog].[deploy_project]
@folder_name = @folder_name,
@project_name = @project_name,
@project_stream = @project_stream,
@operation_id = @operation_id OUTPUT;
SELECT @message = om.message
FROM internal.operation_messages AS om
WHERE
om.operation_id = @operation_id;
IF @@ROWCOUNT > 0
BEGIN
PRINT @message;
END GO
8 Run this query on production (or get the deployment process to do it) – results as below.
9 If I look at the catalog on production server now, I can see that the packages I did not want are gone from there.
