Poll: Store SSIS pkgs as file or in MSDB?

Question

Poll: Store SSIS pkgs as file or in MSDB?

LeeFAR

SSCarpal Tunnel

Points: 4644
More actions
November 2, 2006 at 7:28 am

#114173

For those of you out there that are using SSIS heavily, do you store your packages as files or in the MSDB? And why do you? What are the pros and cons?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

kenambrose SSCrazy Points: 2513 More actions · Answer 1

files only. for the following reasons: it avoids the need to have a Sql Server installed and running as part of the ET solution (frees up memory,much simpler to deploy and maintain, and more reliable - less that can break).

DanKennedy Hall of Fame Points: 3736 More actions · Answer 2

We also use files only. It's easier to manage versions etc with your source control product of choice (SourceSafe, Subversion etc).

Dan
www.firstcs.co.uk

NULLgarity SSCommitted Points: 1572 More actions · Answer 3

I've been using MSDB so packages are backed up with database backups.

Twitter | LinkedIn

debrucer1 SSCertifiable Points: 5366 More actions · Answer 4

files only... for all of noggin's reasons.

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

LeeFAR SSCarpal Tunnel Points: 4644 More actions · Answer 5

Thank you to kenambrose, noggin, BEACHDBA, and David Russell for replying. Do any of you use package configurations and logging? If so, how do you store this type of data? Also, do you typically have a folder for each package where you store the package and related files? I would assume you secure the folders through AD security. And one last question. How do you typically deploy the packages and related files to your servers? Manual file copy, deployment package, etc..

Thanks again for your interest in this post.

NULLgarity SSCommitted Points: 1572 More actions · Answer 6

I use xml configuration files and text log files stored in the default path in a folder specific to the package.

I deploy with the deployment package just for the reminder to tweak the config files and the GUI for doing so.

I'm a relative SSIS newbie though, so I'm not claiming to do things the best way, just relating how I do them.

Twitter | LinkedIn

kenambrose SSCrazy Points: 2513 More actions · Answer 7

re. configurations. I use them but carefully, otherwise they can be a real headache during development. The gotcha with them is that once implemented, later changes to related objects in BIDS are _not_ reflected in the existing config files. And when you kick-off a debug test run from BIDS, the _old_ values from the existing config files are used by BIDS for the package, even if newer values have been entered in BIDS!. Very confusing if you don't understand it.

The upshot is that you don't implement configuration files for a solution until all testing and dev are completed- implement them instead just before you deploy.

And if you have to maintain your solution using the design environment, first delete the associated config files, make all your changes, and again, just before deployment of the updated packages, instantiate new config files and deploy the new config files along with the updated package...