Simple sql code deployment utility

I'd to do code deployment of sql scripts to production server from my development server for one of my projects recently. The project had a large number of sql scripts which had to be deployed to live server. For easy and automated deployment of these scripts to the server I created a deployment utility using SSIS package. I'm posting it here so that somebody else with same problem will get benefited.

The package will be as simple as shown below

As shown, the package consists of a For Each Loop container and a Execute SQL task inside it. The For Each loop is for iterating through the sql script files which are present inside common folder. The configurations will be as shown below. I've added flexibility by including a variable through which user can determine the folder at runtime.

The variable @[User::DirectoryPath] determine the source folder for deployment at runtime

We will create another variable to hold file name during each iteration and map it inside for each loop

The Execute SQL task will execute the actual script within the files and will be setup as follows

As you see the sql task will read the from file pointed by file connection. The file connection will set up using a variable for dynamically taking path from for each loop.

Once such a package is setup you can use a procedure to call this package interactively to pass the configuration values through parameters.

Given below is a procedure that I've created for calling the package programmatically.

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='ExecutePackage')

DROP PROC ExecutePackage

GO

CREATE PROC ExecutePackage

@ServerName varchar(100),--Target server for deployment

@DBName varchar(100),--Target db for deployment

@SourceFileFolderPath varchar(1000),--Folder path containing sql scripts

@PackagePath varchar(1000),--package file path

@Debug int=0--debug variable to check command

AS

DECLARE @Cmd AS varchar(8000)

SET @Cmd= COALESCE(@Cmd,'') + '" /set \package.variables[DirectoryPath].Value;"\"' + @SourceFileFolderPath + '""'

SET @Cmd=@Cmd+ '/set \package.connections[dbname].Properties[ConnectionString];"\"Data Source=' + @ServerName + ';Initial Catalog='+ @DBName +';Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package-{4767D8A5-3079-473E-87F6-5E45C19DD882}server.dbname;Auto Translate=False;\""'

SET @Cmd = 'DTEXEC /F "' + @PackagePath + @Cmd

IF @Debug=1

PRINT @Cmd

EXEC xp_cmdshell @Cmd

GO

then you can call this by passing suitable values for destination server,database, source folder and package folder paths.

I've just given you a simple code deployment utility. You can extend it to add more functionality and even plug this to UI to make it more presentable.