What are SSIS packages deployment and change-tracking best practices?

Question

I've got quite a robust development environment regarding my SQL database Schema and Data (everything is source controlled, deployment is automated, etc), but when it comes to SSIS packages, the process is quite tedious and totally manual.

• How can I automate the SSIS package upgrade process (right now, I copy my packages manually to each server, and then I use Management Studio's "SSIS Package Upgrade Wizard")

• How can I deploy my Schedules (right now, I'm doing it manually)

• How can I deploy my SQL Agent Jobs (right now, I'm doing it manually)

• How can I get my Schedules and Jobs int my TeamSystem repository (right now, I use no source-control for this)

• Are there other things I can/should do to enhance the way I work regarding SSIS packages ?

I'm using MS SQL Server 2008 SP1, but I imagine some advices could be useful on other versions as well.

Answer 1

On point 1 (automating deployment):

It's possible to deploy SSIS packages from the command line using dtutil.exe.

The following example would deploy the local file c:\test.dtsx to MSDB storage on server1 with the name test_package:

dtutil.exe /FILE C:\test.dtsx /dests server1 /COPY SQL;test_package

It will do any combination of import/export for all the various SSIS storage options, if you can work out the combination of parameters to use.

This may not be relevant to you, but what this won't do (that the wizard will) is copy configuration files; you'd need a separate batch step for that.

On points 2 and 3 (source control of SQL Agent):

As others have said, SQL Agent Jobs and schedules can be scripted as T-SQL (in Management Studio, right-click the Job and select Script job to...), held in source control, and deployed like any other T-SQL script.

Our DB source control tree is set up with one folder per production server containing one subfolder per user database plus an extra subfolder called _config which contains scripts for server-level objects such as logins, linked servers and SQL Agent Jobs.

(It's not really relevant to your question but we store SSIS packages in their own tree, since they can be applied to multiple servers)

We don't use TFS, so I can't comment on the specifics of getting the scripts into it.

Answer 2

Well what we do is save everything in source control. Our SSIS packages are saved as DTSX files and those are put into our source control. We use the source control versions of everything to promote to QA and Prod. We do all our configuration for SSIS packges using configuration tables in a database we have set up to manage meta data for SSIS packages. This generally consists of all our connection strings as well as some variables we might want to set at runtime. We also have all our logging set up in this database as well. One of the beauties of this is it becomes far easier to upload the packages that are being changed because the configuration tables do not usually need to be changed and all that is needed is to load the revised dtsx file to the location it will run from. And even if we need to add a new refernce to the configuration table, this is an insert into a table and easily scripted for the upgrade process.

You can also use configuration xml files to change your configurations easily (these can also be stored in source control), but we prefer having everything in the ETL database where we store the meta data.

Answer 3

1. I don't know if upgrading the package can be automated at all.
2. To deploy SSIS packages create a Manifest file
3. You can write SQL scripts to create SQL schedules and jobs and then call this script using some vb.net script or something.
4. To add SQL scripts to TFS, open SQL Server Management Studio > File > New > Project > SQL Server Scripts. Add Scripts to the Queries folder and then later you can add this solution to the TFS. I have never tried this. Instead I copy the script files to TFS manually.

In my opinion there is no point in creating a full fledged deployment utility for deploying SSIS packages as this is a very rare activity. Deployment/installation should be simple when we distribute a software product to non-tech users so that it is user friendly. But an SSIS package will be always handled by a DBA or sort of person who already knows how to use it...

Answer 4

For point 1 see this site http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.html

I had the same question a while back and this site helped a lot. I am curious about the other points as well as my team deploys them manually like you but I thought I'd share what I knew.