When developing SSIS package, very often, the development environment is different from the testing environment as well as the production environment. Different environment would probably have different connection strings for different connections. They can be database connection string, flat file location path etc. To make elevation easier, connection information under Connection Manager are designed to be configurable when the package is being registered under SQL Agent. Moreover, initial value of the variables can also can configure when attaching the package as part of the SQL job.

In the blog, I’ll show you some How to deploy SSIS packages for SQL Agent to execute with custom configuration.
I’ve created a simple SSIS package that take a global variable “env” and write it to a csv file along with a timestamp obtain from a database. Here’s a copy of the dtsx for download: demo.dtsx

Job Creation

When creating a job to execute a SSIS package, you can either use the GUI from SQL Server Management Studio or by SQL query. I usually use the graphic interface first, then extract it as a SQL script and modify it from there. Let’s try it out together!

Open Microsoft SQL Server Management Studio and connect to the database instance where you want the job to be located. Once connected, under SQL Server Agent, right click on jobs and choose to create a new job. After entering a job name and the owner name, hit “Step” on the left menu bar and click on the “New” button for a new step.

On the Job Step Property screen, choose SQL Server Integration Service Package for type and File System for package source. Note that we’re using File System in this demo for simplicity, however, you may save the package into the ssis package store on SQL Server and select the package from there.

My database instance is localhost, so I simply reference the package as local file on the system. If the database instance you connects to is not your localhost, you can simply create a network share and reference your dtsx file like: \\yourhostname\shareName\demo.dtsx.

Please note that the job we’re setting right now is purely for development purpose, and the location of the package can be modify when we deploy to another environment.

After setting the General tab, let’s move onto the Data sources tab.

In this tab, you can specify connection strings for each of the connection managers in the package. These are the connection information SQL agent is going to use when executing the package. Since we’re creating this job for development testing, let’s select the two connection managers and we’ll customize the values when we generate the elevation scripts for testing and production environments.

In this SSIS package, a global variable named “env” is set to “DEV” by default, we’ll specify the value once again on the “Set Values” tab for the elevation script.

Please take a note on the syntax: \package.variables[env].Value = “DEV”

Now we can press OK twice to save the job.

We may try running the SQL job manually to see if it runs ok. The next step for us is to prepare elevation script to different environments. Let’s right click on the job, and generate the SQL script used to create this job.

The script would look something like this:

USE [msdb]
GO
/****** Object:  Job [testJob]    Script Date: 09/15/2009 01:19:13 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/15/2009 01:19:13 ******/
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=N'testJob',
		@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=N'DC\Jack Wong', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Execute demo SSIS]    Script Date: 09/15/2009 01:19:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute demo SSIS',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@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=N'/FILE "C:\projects\ssisVariableDemo\ssisVariableDemo\bin\demo.dtsx" /CONNECTION "localhost.master";"Data Source=localhost;Initial Catalog=master;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION result;"c:\result.csv" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\package.variables[env].Value";"""DEV""" /REPORTING E',
		@database_name=N'master',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
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:

For elevation to different environment, we’d probably need to modify the connection manager strings and the default value for the global variable “env”. Also, the owner of the job would probably need to be modified as well.

On line 25, you may update the @owner_login_name to set the owner of the job

On line 38, the @command parameter is to specify any runtime specific customize values and configurations. There are three things you can modify.

• The ssis package source: you may specify the source type and location. Thus your ssis package can be elevated and staged and needs no changes for different environments
• Connection Manager – connection strings: You may specify a new location of the csv file and/or a new database with user credential
• Variable values: You may specify initial values for any variables within the package.

This is it for this blog. I hope it can be helpful to you.