---
title: Script deployment of SSIS packages from folder
date: 2012-03-08 14:14:00 +0200 +0200
draft: false
author: John Roos
----

This is a very easy way to automate something which can take quite some time to do manually if you have lots of environments. Its made as easy as possible in this example rather than fancy and complicated. This script will create a SQL Server Agent Job which will execute a number of SSIS packages (dtsx files). The script will look for dtsx files in the selected folder DTSVirtualPath and also a config file (dtsConfig). Each file will get its own step in the job in alphabetical order. If you want to have a different order you could just rename the files with a number in the front or rearrange it manually afterwards in the job properties.

To make it as easy and quick as possible I got the base for the script by using one of the jobs which was already configured. Just right click on a job which already exist and select Script Job as, Create to, New Query Editor Window. I then edited the script to make it a bit dynamic. I included two schedules for the job in case schedules are needed. Just edit the highlighted lines to adapt to your environment and give it a go. I have only tested this on SQL Server 2008 R2 but it will probably work on 2005 with a few or no tweaks.

Before running the script you need to enable xp_cmdshell. This is because the script is running the dir command to get the file names for the dtsx files. To enable xp_cmdshell run the following:

-- Enable advanced configuration options
EXEC sp_configure 'show advanced options', 1
 
RECONFIGURE
 
-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
 
RECONFIGURE
 
-- Test the feature
EXEC xp_cmdshell 'dir c:\'

This can easily be incorporated into the script below if you dont want to have it enabled all the time. In the end of the script you can simply disable it again.

@DTSVirtualPath is the path from the “root” folder in the SSIS Package Store. In this case its using the default path: C:\Program Files\Microsoft SQL Server\100\DTS\Packages

USE [msdb]
GO
 
DECLARE @DTSJobName nvarchar(255)
DECLARE @DTSServerName nvarchar(255)
DECLARE @DTSVirtualPath nvarchar(255)
DECLARE @DTSConfigFileName nvarchar(255)
DECLARE @DTSOwnerName nvarchar(255)
 
-- USER VARIABLES
 
SET @DTSJobName = 'My SSIS job'
SET @DTSVirtualPath = '\FolderWithdtsxFiles\'
SET @DTSConfigFileName = 'config.dtsConfig'
SET @DTSServerName = 'Servername'
SET @DTSOwnerName = 'domain\user'
 
 
-- USER VARIABLES END --
 
DECLARE @DTSFolder nvarchar(255)
DECLARE @DTSConfig nvarchar(255)
DECLARE @DTSCommand nvarchar(1024)
DECLARE @TotalRows int
DECLARE @CurrentRow int
DECLARE @DTSFileName nvarchar(255)
DECLARE @TempDTSsteps TABLE(Seq int IDENTITY(1,1),DTSfile nvarchar(255))
 
SET @DTSFolder = '\File System' + @DTSVirtualPath
SET @DTSConfig = 'C:\Program Files\Microsoft SQL Server\100\DTS\Packages' + @DTSVirtualPath + @DTSConfigFileName 
 
-- insert all the job steps/dts files into the temp table
-- Add more rows below if there are more steps to be added
-- The number have to be unique and represents the order of the steps
 
DECLARE @Command nvarchar(1024) 
SET @Command = 'dir "C:\Program Files\Microsoft SQL Server\100\DTS\Packages' + @DTSVirtualPath + '*.dtsx" /A-D  /B' 
 
INSERT @TempDTSsteps
EXEC MASTER.dbo.xp_cmdshell @Command
 
DELETE FROM @TempDTSsteps WHERE DTSfile IS NULL
 
 --SELECT DTSfile FROM @TempDTSsteps
-- inserting steps complete
 
 
SELECT @TotalRows=count(*) from @TempDTSsteps
SET @CurrentRow = 1
 
-- Create Job
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@DTSJobName, 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'[Uncategorized (Local)]', 
  @owner_login_name=@DTSOwnerName, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
DECLARE @SuccessAction int
SET @SuccessAction = 3
 
-- loop start
WHILE @CurrentRow <= @TotalRows
BEGIN
SELECT @DTSFileName = REPLACE(DTSfile,'.dtsx','') FROM @TempDTSsteps WHERE Seq = @CurrentRow
 
IF @CurrentRow = @TotalRows
BEGIN
 SET @SuccessAction = 1
END
 
SET @DTSCommand=N'/DTS "' + @DTSFolder + @DTSFileName + N'" /SERVER ' + @DTSServerName + N' /CONFIGFILE "' + @DTSConfig + N'" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
-- Create step in job
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@DTSFileName, 
  @step_id=@CurrentRow, 
  @cmdexec_success_code=0, 
  @on_success_action=@SuccessAction, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'SSIS', 
  @command=@DTSCommand, 
  @database_name=N'master', 
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CurrentRow = @CurrentRow + 1
END
-- end loop
 
-- Set starting step
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
-- configure schedules and commit transaction
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailySchedule', 
  @enabled=1, 
  @freq_type=4, 
  @freq_interval=1, 
  @freq_subday_type=1, 
  @freq_subday_interval=0, 
  @freq_relative_interval=0, 
  @freq_recurrence_factor=0, 
  @active_start_date=20120101, 
  @active_end_date=99991231, 
  @active_start_time=2000, 
  @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklySchedule', 
  @enabled=1, 
  @freq_type=8, 
  @freq_interval=1, 
  @freq_subday_type=1, 
  @freq_subday_interval=0, 
  @freq_relative_interval=0, 
  @freq_recurrence_factor=1, 
  @active_start_date=20120101, 
  @active_end_date=99991231, 
  @active_start_time=220000, 
  @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO