July 20, 2010 at 3:45 am
Hi all,
Where can I find the difference between FIleSystem and MSDB for storing the SSIS packages.
John
July 20, 2010 at 5:42 pm
Hi John,
I've tended to use filesystem which has the advantages of:-
1) Not bloating MSDB if you have thousands of packages.
2) Ease of deployment of packages - DTSX files can simply be copied into the filesystem using Windows Explorer.
3) Multi-user security - using NTFS permissions within the filesystem it's possible to grant rights to over-write packages to more than one person. Useful if you have teams of developers all wanting to deploy packages to specific folders but not others.
MSDB I think has the advantages of:-
1) A database backup of MSDB will automatically safeguard all SSIS packages. No further messing about with file-system backups needed.
2) Ability to use the roles within MSDB specifically designed to allocate granular rights to SSIS packages - although I believe that these are more geared around a designated package owner as opposed to multiple people accessing the package.
I spoke to someone who has done a lot of SSIS consultancy and he told me that the split of package stores used by people is about 70% filesystem to 30% MSDB.
My choice at the moment would be filesystem, although I heard a rumour that in the future Microsoft may be including more functionality within the MSDB package store - so perhaps those of us not storing our packages within MSDB will want to move them in there.
July 20, 2010 at 6:38 pm
I prefer to use msdb. It allows to have folders hierarchy and keep track of versions. I have report that displays all packages with versions numbers and hierarchy. Also, as DBA I prefer to protect SQL Server packages using database roles.
I think that for the development environment file system is the best, but for the production one - msdb.
Here is more information:
July 20, 2010 at 10:40 pm
Thanks.
Really what I wanted.
John
July 20, 2010 at 10:52 pm
magasvs (7/20/2010)
I prefer to use msdb. It allows to have folders hierarchy and keep track of versions. I have report that displays all packages with versions numbers and hierarchy. Also, as DBA I prefer to protect SQL Server packages using database roles.I think that for the development environment file system is the best, but for the production one - msdb.
Here is more information:
This is more or less the same methodology I employ. I really like the versioning provided with storing it in msdb over file-system. Of course there is also a need to store the packages in source control when in a structured file as opposed to storing it in the msdb.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2010 at 6:40 am
I'm surprised no one has mentioned the one drawback of storing packages in the database: you cannot edit them there. You have to export the package to the file system, edit, and re-import the package. If you're going to do that anyway, it's just as easy to store them on the file system.
That said, I find lately I have been importing them into the DB anyway. That way, I have a backup of everything I need to restore the SQL server if I need to, without having to re-create folder structures. It's not a well-thought out approach from Microsoft, IMHO. If a table, SP, trigger, etc. is stored in the DB, then you edit it there. Why not SSIS?
HTH,
Rich
July 23, 2010 at 6:44 am
Personally, I like to store the package as files because I can simply ZIP up the whole thing to make a snapshot for version archiving and/or for easy FTP transfer between the development and production systems. I use 7.Zip on the whole directory; it's free and you get extremely good compression on SSIS package files.
Files are also good if you're trying to do a global edit or search to change a table or variable name, and you need to be sure you've caught all the places where you used the old name. I use Notepad++ which can search/edit a whole file tree. (Yes, global edits can be dangerous, but I can always revert to the previous version simply by unzipping my snapshot.)
July 23, 2010 at 12:16 pm
That is why I love SQL Server, you have so many options to do the same thing and it just comes down to your personal preference in handling the task.
August 4, 2010 at 2:38 pm
rmechaber (7/22/2010)
I'm surprised no one has mentioned the one drawback of storing packages in the database: you cannot edit them there. You have to export the package to the file system, edit, and re-import the package. If you're going to do that anyway, it's just as easy to store them on the file system.
Packages are edited in Visual Studio on the file system and imported to SQL Server. As long as they aren't deleted from the file system, there's no need to export before editing; just edit and re-import. You shouldn't need to export unless the source files are lost or corrupted.
August 5, 2010 at 6:15 am
wilson.higdon (8/4/2010)
rmechaber (7/22/2010)
I'm surprised no one has mentioned the one drawback of storing packages in the database: you cannot edit them there. You have to export the package to the file system, edit, and re-import the package. If you're going to do that anyway, it's just as easy to store them on the file system.Packages are edited in Visual Studio on the file system and imported to SQL Server. As long as they aren't deleted from the file system, there's no need to export before editing; just edit and re-import. You shouldn't need to export unless the source files are lost or corrupted.
If someone edits the dtsx file on the file system and doesn't import it into SQL Server, there's no way I'm aware of (if you aren't running version control software) to know that the file on SQL matches the one on the SAN. To be sure, you need to re-export from SQL.
I would still like to see MS implement a way to edit the package directly from the SQL server. Having two copies is a conflict waiting to happen.
Rich
August 5, 2010 at 7:15 am
If someone edits the dtsx file on the file system and doesn't import it into SQL Server, there's no way I'm aware of (if you aren't running version control software) to know that the file on SQL matches the one on the SAN.
sysdtspackages90 table has information about SSIS packages versions (major and minor), so you can see this information. You can also use /VerifyB[uild]major[;minor[;build]] parameter when you run package if you want to make sure that only specific version allowed in production.
August 6, 2010 at 7:58 am
In SQL Server, right-click the installed package and select Reports -> General to see the creation date and version to compare with the one on the file system.
August 6, 2010 at 8:04 am
We use an enterprise scheduler and not SQL Agent to schedule our jobs - and storing Packages in File System makes it easy to invoke them from the Scheduler.
February 17, 2011 at 12:07 pm
Referring to post from rmechaber.....
This is a good point, since I have stored them in the database, it take many more steps to update the package. Therefore, I'm running older package versions without even knowing it.
I was wondering why my changes were not taking effect after;
Creating the package in Visual Studio
Save the package in Visual Studio
Import the package in Intergration Services to MSDB
Run the package via a windows schedule task
It runs the old version of the package
This has caused headaches and more work....
Thank you for pointing this out.....Nice job....
:w00t:
February 17, 2011 at 12:13 pm
Referring to post from wilson.higdon
This is what I'm doing and the generic report (generated on the server (Intergration Services)) on the package (after importing it again) shows that it is the older version of the package... Verified by the Package Creation Date..... Why is this????:crying:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply