Pros vs Con Storing SSIS Packages in the File System vs MSDB

Question

Pros vs Con Storing SSIS Packages in the File System vs MSDB

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 16, 2012 at 2:52 pm

#249351

Could someone share with me the Pros vs Con Storing SSIS Packages in the File System vs MSDB?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 10 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

One benefit I like of storing in MSDB is that the changes get versioned within msdb. Plus, I can backup the msdb database and know that those packages have been backed up as well.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I prefer to store DTS & SSIS Packages in MSDB but where I currently work they use the file system.

It may be that they perceive it as easier to mange connection information, etc?:unsure:

To store it in MSDB I probably would need more ammunition.

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 3

storing them in msdb keeps your SSIS packages together with your other SQL objects, it also means you have a ready made backup mechanism for the packages as long as you backup your msdb, and that mechanism is under the control of the DBA. Security is also better in msdb via the msdb roles.

File system storage is simpler, and easier to understand for a non-DBA, recovery of packages is perhaps simpler, as long as you have file system backups taking place.

---------------------------------------------------------------------

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

Just curious

Are the SSIS packages being source controlled?

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Thanks.

I do not like the File System.

I only use it in BIDS and deploy it to the server.

I need to think of other reasons.

I suspect that they do it because that is the way that has always been done.:rolleyes:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281344 More actions · Answer 6

Welsh Corgi (1/16/2012)
Thanks.
I do not like the File System.
I only use it in BIDS and deploy it to the server.
I need to think of other reasons.
I suspect that they do it because that is the way that has always been done.:rolleyes:

I'd agree.

I prefer msdb because I can "lock" it down somewhat. But the real advantages for me is that I can be certain of the backups, I can move the packages very easily from one db server to another, and the versioning. Everything else is more opinion and preference based and really won't win any arguments - methinks.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

SQLRNNR (1/16/2012)
Just curious
Are the SSIS packages being source controlled?

I hope.:w00t:

Good question but I have a bad answer; I just found them and I have not yet to get a good answer as to where their source control is.

I'm preparing to move this Server and others to a Data Center in a different state.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Welsh Corgi (1/16/2012)
SQLRNNR (1/16/2012)
Just curious
Are the SSIS packages being source controlled?
I hope.:w00t:
Good question but I have a bad answer; I just found them and I have not yet to get a good answer as to where their source control is.
I'm preparing to move this Server and others to a Data Center in a different state.

If there is no source control - I would say that helps make a very big argument point for you (that and ensuring that the backups are good).

You really only need two solid points.;-)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

I have not been there very long but when I first got their I as about source control (not mentioning who), I could swear that did not have it or use it.

From what I have seen believes me to assume that they do have and if they do it is not enforced or used properly.

:w00t:

One thing that I noticed that it seemed kinda easy to change the connections as opposed to doing it the right way and setting variables for dynamic connections.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/