Tag Archives: sqlserver

SSIS: Where & How to Deploy?

Uncategorized, ,

One would think deploying SSIS packages would be a fairly straight forward process.  Heading in to this I would have never guessed that the question, “What is the best way to deploy SSIS files to the SSIS Server?” would pose so much discussion or raise so many questions. However, as I’m finding with most things I research and work on with SSIS development, there is NEVER a single best answer for these types of questions.  Really, it comes down to reading, analyzing, and finding the “best” solution for you and your situation – there is no one best way.  I wish it were more clear cut, but unfortunately there is a lot of grey area to swim through.

For a little background, there are basically three (but really only two) main options for deploying SSIS packages:

  1. File System
  2. SQL Server
  3. SSIS Package Store (really just a version of #1)

Each of these options has certain advantages and disadvantages and none of them are the clear cut winner. Go figure.

Deploying to the File System

The key advantage of this solution is that it is drop dead simple.  All you need to do is take the dtsx files you’ve created in BIDS locally and move them to a directory in the SSIS Server of your choice.  You can then use the DTEXEC executable on the server to run the dtsx files from there.  Additionally, you can schedule them to run via the SQL Agent and set any necessary parameters or command line setting necessary for the file to run correctly.  This is nice because there is no need for complicated configurations or added security, just put the files out there and let them run. 

Another benefit to the file system deployment is backup and recovery.  As long as the file system that holds your packages is properly backed up, recovery from a major melt-down is simple and straight forward.  An additional benefit is the ability to recover single files from the backup rather than the entire directory.

Easy, right?  Yeah.  I agree, it seems like a no-brainer, but – like anything -  there are a few downsides to consider as well.

For starters, any security you would like in place needs to be handled at the file/folder level using Active Directory to assign the access to the directory folders and associated files.  While this isn’t a huge issue nor necessarily bad, I think the SQL Server option provides a better implementation model, increases flexibility, and improves security.  Along with security, there is limited audit ability included in utilizing a file systems deployment.  While it may be possible to track who put a new file in place or overwrote an existing file with a newer version, it is not ideal.   

Deploying to SQL Server

This is definitely the most complicated deployment option, but it does offer the best security options and execution performance that is on par with the other options.  There are also SSIS specific SQL Server roles that can be granted to allow (or not allow) people to see, execute, and interact with SSIS packages stored in the database.  Additionally, the packages can be encrypted within the database which allows for yet another layer of package security.  Not bad.

Backing up SSIS packages stored in the database can be accomplished by backing up the database.  While this is simple and most DBA’s will already have this in place, it does come with its drawbacks.  The primary issue with this is that it is not possible to restore or recover a single SSIS package by using the database backup – the only option would be to restore ALL of the SSIS packages stored in the data base.  This can be rather easily overcome by utilizing a source control product like Microsoft Visual Source Safe to store current version of all packages stored in the database.

Deploying to SQL Server seems to be the best option for our environment.  While it doesn’t solve every problem – and actually creates a few of its own – it seems to me to be the most easily manageable deployment method of the options given.

Deploying to the SSIS Package Store

As the SSIS Package store is really just file location within the SQL Server installation, using the SISS Package Store for deployment has all of the same challenges and advantages as utilizing the file system.  The location of the SSIS Package Store is Program FilesMicrosoft SQL Server90DTSPackages directory. 

Resources

I gathered the above information from a variety of resources, most all of which are listed below.

Additionally, I found the following table from Pragmatic Works insightful at a glance and useful to get a basic idea of the important issues and how the main deployment options perform in regards to those issues.

Functionality

Best in File System

Best in MSDB
Security   x
Backup and Recovery x  
Deployment x  
Troubleshooting x  
Execution Speed x x
Availability x  

Knight’s 24-Hour Trainer, Microsoft SQL Server 2008 Integration Services – Book Review

Uncategorized, , , ,

As part of a recent job transition, I found myself needing to quickly come up to speed with the basics of SSIS 2008.  In searching out a good tutorial to help me get back up to speed with the current technology, I quickly settled on Knight’s 24 Hour Trainer as a book that would meet my needs for a few different reasons.

  1. I was already familiar with Brian & Devin Knight from, well, everywhere.  Seriously, if you are at all interested in learning SSIS or the SQL Server BI stack, you can’t avoid these guys – not that you’d want too.  From BIDN to SQLServerCentral to PragmaticWorks these two are everywhere and well respected.
  2. The book seemed to cover all the basics of SSIS from A to Z in a thorough manner without overkill.
  3. Training videos are included on DVD for each lesson in the book.
  4. At $30 bucks on Amazon, the price is right.

What this Book is

If you are looking for a basic introduction to SSIS, this is your book.  It will walk you through the steps of installing Microsoft Business Intelligence Development Studio (BIDS) and take you all the way to creating, configuring, and deploying SSIS packages for a data warehouse.  The book touches on most all of the major transformations, sources, and destinations within SSIS.  The book also helps guide you towards best practices and using the correct tool for the correct purpose. 

Along with all of this, the book is able to guide you in a very systematic, structured approach to learning.  If you start at the beginning of the book and work your way through it, you will find that each lesson builds on prior lessons.  Additionally, the projects get more and more complex and involved as you progress.  While this is not a revolutionary learning model, it is well executed in this book and serves its purpose well.

What this Book is NOT

Do not buy this book if you are looking for an all inclusive reference for SSIS.  While it covers most of the major topics for SSIS, it would not serve you well as a development reference book. If you are looking for a solid SSIS reference, you’d be much better served buying a book like Professional Microsoft SQL Server 2008 Integration Services also co-authored by Brian Knight.

Finally, the end

I would highly recommend this book as a quick alternative to a basic  SSIS course.  There are loads of hands on examples, it’s laid out very well, and it does a great job of covering the fundamentals of SSIS.  If you are new to the Microsoft BI stack, looking to quickly study up on SSIS, you will enjoy this great resource.