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:
- File System
- SQL Server
- 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 Files\Microsoft SQL Server\90\DTS\Packages 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 |
|
