June 15, 2009 at 11:39 am
Is there a preferred approach to storing SSIS packages when it comes to file systems vs. MSDB? I know there are some pros and cons to each, but don't know if people have found that one method is better then the other.
Your thoughts???
Thanks, Dave
June 15, 2009 at 5:16 pm
We've chosen to store packages as files because we can use configuration files and because the packages seem to be easier to move from instance to instance. We also put the packages in Source Safe, which is easier if they're stored as files.
Greg
June 16, 2009 at 6:21 am
By configuration files are you referring to something like a .ini file containing connection string information or are you talking about something else?
June 16, 2009 at 9:03 am
Configuration files are included in Integration Services and are saved as xml code. They store package variable values, connection strings, configuration info for log providers, etc. BOL has information about them under configurations [Integration Services].
Greg
June 16, 2009 at 9:18 am
It's a management thing. If you run them in MSDB, they are in your backups, and when you restore on another server, they are contained. In the file system, you have to manage them separately, which may or may not be a big deal.
I've tended to keep things in msdb, from a holdover from SQL 7/2000 and DTS, but no real reason.
June 16, 2009 at 9:35 am
Our preference has been to store packages in MSDB, but we don't have a great deal of experience with SSIS so we aren't sure of all the pros and cons. The developers are concerned that storing packages in MSDB makes managing connections difficult, because you have to store each unique connection as a separate object. We're not sure if they are correct. I didn't think connection management was difficult in SSIS, but perhaps I'm mistaken.
How do you handle migrating packages from development to test to production? One concern the developers have is the work required to change the connection information to reference a different set of servers when migrating from development to test and from test to production.
Does SSIS still work like DTS when it comes to the client baring the hardware resource burden for certain functions such as transformations, file import/export? I believe SSIS still uses much of the old DTS engine behind the scenes.
Thanks, Dave
June 16, 2009 at 10:08 am
The beauty of configurations is you can use them to update connection managers for different environments at run time so it's easy to move packages from dev to test to prod.
While you can store packages anywhere, SSIS does the work on the server where Integration Services is stored. The key idea is that Integration Services is decoupled from SQL Server, unlike DTS.
Greg
June 17, 2009 at 1:29 am
Our preference is to store a copy of the package in msdb because we can "Rely on server security" and save the connection details and passwords, especially good when importing from non-SQL Server databases.
Never encrypt with a user key as only the one user can access the package. When we do save dtsx files with a password we have set a corporate password to use for all packages so we can always access another developer's work.
One of the joys of SSIS is that packages can be stored on any server and run from the sql agent on a different server to work with data on yet another server or three!
June 18, 2009 at 1:20 am
We could not get SSIS configuration files to work when the package is stored in MSDB.
Admittedly this might be due to a lack of knowledge, but I have tried once or twice since and always given up and gone back to storing the package and its configuration file in the file system.
Has anyone had success with using XML configuration files with packages stored in MSDB?
June 18, 2009 at 6:18 am
Not sure if this is what you are talking about, but...
I store my pkgs in msdb. I use package configurations to ease
in deploying to diff environments. Then we have an environment variable on each box (dev,qa,prod) that is used in conjunction with the pkg configuration to set the connections all by itself.
Has worked very well.
June 18, 2009 at 8:28 am
P Jones (6/17/2009)
Our preference is to store a copy of the package in msdb because we can "Rely on server security" and ...
I agree with PJones, we use msdb and specify "Rely on server security" when saving the packages to MSDB. This option is available when using "SAVE COPY of package AS" or when deploying the package with Manifest File. This method relies on the server for security and you don't have your packages floating out there on the server. I also agree with the other post that storing packages to MSDB gives you added protection because the packages are backed up with the MSDB database backup and can be restored to another database if needed.
Dave Coats
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply