BT

Using SSIS in a Team Setting

by Jonathan Allen on Aug 10, 2007 |

SSIS or SQL Server Integration Services, can be thought of as a graphical programming language specifically designing for moving and processing data. It replaces DTS packages as one of the primary ways to move large amounts of data into and out of SQL Server from other data sources such as flat files and non-Microsoft databases.

What follows is a summary of the Jamie Tomson's experience using SSIS in a team setting.

The first problem with SSIS packages is that they are versioned as a unit. Unlike a normal code file, you cannot have two people modify the package at the same time and expect to be able to merge your changes together. The underlying XML format simply isn't designed to be human readable. The impact of this can be lessened by using exclusive locks when you check out a package. Another mitigating factor is the ability to call packages from other packages via the Execute Package Task.

Jamie Tomson highly recommends using package templates from the very beginning.

If you only take one piece of advice from this blog entry make sure its this - use templates; they are vital in a multi-developer environment. Templates are the only built-in mechanism for enabling common approaches to those mundane but necessary aspects of SSIS development like logging, annotating, checkpointing and configuration. Spend time at the beginning of your project deciding how you are going to accomplish logging, checkpointing and configuration and build an appropriate template to support those decisions. I have provided an example template here. Don't leave home without one.

Annotations and naming conventions are also very important to document exactly what a package is trying to accomplish.

In regards to projects and solutions, Jamie suggests:

Use separate projects to group together packages that work in concert with each other or collectively achieve a single goal. Don't be afraid to introduce more projects but only do so where it makes sense to. If you can, use a single solution for all your SSIS packages.

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2014 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT