Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2009-06-15 : 13:40:30
|
| 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 |
|
|
svicky9
Posting Yak Master
232 Posts |
Posted - 2009-06-16 : 07:15:46
|
| Its all about Package Maintenance.I think storing on MSDB would be a good option if you frequently backup the MSDB database..its also good for deployments from the BI Studio..The packages are maintained in the SQL SERVER. so when you develop something you deploy it on the server..You still need to maintain your packages on some subversion or Data Dude..But you have a copy of your package on the live system.(High Availability)When using the filesystem you dont need to deploy the packages to the server but you need to develop them and maintain them on the same place(may be?)..The maintenance in this scenario is done by the folder and file system. You go to make sure you are the System Administrator in maintaining permissions and all other stuff.If you are only a DBA and not the System Administrator for the server its better use MSDB as you can maintain them.http://www.sqlserver007.com |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-06-16 : 08:32:28
|
| The developers are concerned that storing packages in MSDB makes managing connections difficult. They say you have to store each unique connection as a separate object. We need to get a better understanding from them about this specific concern, but I didn't think connection management was difficult in SSIS. Am I mistaken? Can't connection properties be stored in a central file such as a .ini file?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.Lastly, with DTS when a client executes a package much of the processing is performed on the client machine. Is this still true with SSIS?Thanks, Dave |
|
|
|
svicky9
Posting Yak Master
232 Posts |
Posted - 2009-06-16 : 10:27:44
|
| Connections can be set dynamically I generally store in Tables or in some config files or in Environment Variables..All you go to do is set the connections in Package Configurations..if you have different environments set your environment variables on each server to point to the configuration files which in turn will affect the SSIS packages dynamically..SSIS executions happen on the Server side..http://www.sqlserver007.com |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2009-06-16 : 11:29:46
|
| So Microsoft changed the way SSIS packages execute vs. DTS, where much of the processing was on the client? For stored procs executed by DTS packages, the hardware resources on the database server were used, but for data transformations, file imports/exports and a few other functions the client machines resources were hit more then the DB server. Thanks again |
|
|
|
|
|
|
Should packages be stored in file system or msdb?