Archive

Archive for June, 2012

Automating SSIS 2012 ‘Project’ Deployment

Lets cut to the chase and talk about how one can deploy packages or enable CI with SSIS 2012 projects. This short blog is focused on deploying SSIS ‘projects’ as a whole rather than using DTUTIL, XCOPY etc.

Creating ‘tear down and rebuild-able’ artifacts

We all know that SSIS packages are nothing but mere XML files, right?. Technically yes, but they are actually way much more than that. But, since they don’t have properties of compiling as normal code, we need alternate ways of deploying these files or projects as a whole. The most obvious method would be XCOPY or DTUTIL. With the ‘project’ based deployment of SSIS we can do better. Better how? Deploy the project instead of individual files. When the project based deployment is selected; the build action on the project creates an “.ispac” file which can be used as an msi installer to deploy packages on other environments. But if this action of deployment needed to be automated or enabled for CI we need a way around the UI based aspect of the ispac file (deployment file).

I am assuming the reader is using project based deployment and is familiar with the ispac (integration services deployment file) file used for deployment with SSIS 2012. If not, please read this article http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/10/introduction-to-ssis-projects-in-denali.aspx

The readiness steps needed to run this “.ispac” from the command line are

1. Recreating the integration services catalog: When projects get deployed using the UI based approach with the ispac file, there is an option to deploy the project to a specific integration services catalog (in a SQL Server 2012 instance) and one also gets the option to overwrite it. This needs to be in a script runnable in sqlcmd or powershell.

Tear Down:

IF EXISTS(SELECT 1 FROM SSISDB.internal.folders WHERE name = ‘CatalogName’)

BEGIN

       IF EXISTS(SELECT 1

                 FROM SSISDB.internal.projects

                 WHERE folder_id = (SELECT TOP 1 folder_id

                                   FROM SSISDB.internal.folders

                                   WHERE name = ‘CatalogName’))

       BEGIN

              EXEC [SSISDB].[catalog].[delete_project]

                     @project_name=N’SSIS’,

                     @folder_name=N’CatalogName’

       END

 

       EXEC [SSISDB].[catalog].[delete_folder]

              @folder_name=N’CatalogName’

END

Re-create:

Declare @folder_id bigint

EXEC [SSISDB].[catalog].[create_folder]

       @folder_name=N’CatalogName’,

       @folder_id=@folder_id OUTPUT

 

EXEC [SSISDB].[catalog].[set_folder_description]

       @folder_name=N’CatalogName’,

       @folder_description=N’OptionalDescription’

 

2. Deploying the project from the command line:  The next step is to prepare the command line executable of the deployment file (ispac). The ispac deployment file can be evoked from the command line using IsDeploymentWizard.exe with the following switches.

 

image

 

Ex: IsDeploymentWizard /S /SP:"C:\SSIS.ispac" /DS:"<myServerName>\<Instancename>"

/DP:"/SSISDB/SSIS"

Summary

As simple as that. Just have this two steps incorporated in your CI build steps or a batch file and you are all set. If you are looking for more information on IsDeploymentWizard, MSDN is a good resource. If you encounter any issues do let me know. More than happy to help.