SSIS Best Practice Regarding Storage
-
We're about to migrate from SQL Server 2008R2 to SQL Server 2016. We have SSIS packages in both the package store and the file system. I want to establish a standard beginning with this migration so that we always put packages into the same location. So, package store, or file system, and why?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
We're about to migrate from SQL Server 2008R2 to SQL Server 2016. We have SSIS packages in both the package store and the file system. I want to establish a standard beginning with this migration so that we always put packages into the same location. So, package store, or file system, and why?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Since the package store allows you to store your packages in either the File System or the Database I suspect the question you you actually want an answer for is whether to store your packages in the File System or the Database. The package store itself is more of a management solution. Pros for Database: One point of backup, and probably easier to handle in a multiple instance system. Easier to use from any server or client. Pros for File System: Easier to export/import between systems. Just copy the files. I suspect it's mostly depends on how you work and develop your packages, I would also guess there are security concerns to think about.
Wrong is evil and must be defeated. - Jeff Ello
-
We're about to migrate from SQL Server 2008R2 to SQL Server 2016. We have SSIS packages in both the package store and the file system. I want to establish a standard beginning with this migration so that we always put packages into the same location. So, package store, or file system, and why?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
So, package store, or file system, and why?
A file-system is preferred for files that are manipulated a lot. A database is preferred for files that are mostly read and not changed. My "why" is based on the Dokan-drive experiment; a database will grow quick if you keep editing small files. It is convenient OTOH to have all your files in a single db-file.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
John Simmons / outlaw programmer wrote:
So, package store, or file system, and why?
A file-system is preferred for files that are manipulated a lot. A database is preferred for files that are mostly read and not changed. My "why" is based on the Dokan-drive experiment; a database will grow quick if you keep editing small files. It is convenient OTOH to have all your files in a single db-file.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
The only thing consistent about our data is change. Requirements sometimes change on an hourly basis. File system packages FTW.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
The only thing consistent about our data is change. Requirements sometimes change on an hourly basis. File system packages FTW.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013FS, in an Active Directory and with regular backup :thumbsup: You could opt for a database if you have a lot of pictures or music that doesn't change; in that case, a NOSQL db might perform better than SQL Server.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
FS, in an Active Directory and with regular backup :thumbsup: You could opt for a database if you have a lot of pictures or music that doesn't change; in that case, a NOSQL db might perform better than SQL Server.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
I work for the DoD. We use Sql Server. Period. That will not change for the foreseeable future. None of our data involves pictures/music (we don't write mobile crapps).
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I work for the DoD. We use Sql Server. Period. That will not change for the foreseeable future. None of our data involves pictures/music (we don't write mobile crapps).
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
I work for the DoD. We use Sql Server. Period.
Yes, unlimited money brings unlimited idiocy :thumbsup: Most of us need to be efficient though.
John Simmons / outlaw programmer wrote:
None of our data involves pictures/music (we don't write mobile crapps).
Your department might not, but the DoD does. They even create computer-games :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
John Simmons / outlaw programmer wrote:
I work for the DoD. We use Sql Server. Period.
Yes, unlimited money brings unlimited idiocy :thumbsup: Most of us need to be efficient though.
John Simmons / outlaw programmer wrote:
None of our data involves pictures/music (we don't write mobile crapps).
Your department might not, but the DoD does. They even create computer-games :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
This isn't about efficiency or idiocy. I have a specific set of environmental conditions (sql server and ssis packages). The question is about the best way to store them. None of the rest of the stuff you're talking about matters. At all.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
This isn't about efficiency or idiocy. I have a specific set of environmental conditions (sql server and ssis packages). The question is about the best way to store them. None of the rest of the stuff you're talking about matters. At all.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
This isn't about efficiency or idiocy. I have a specific set of environmental conditions (sql server and ssis packages).
It's not an environmental condition, it is policy.
John Simmons / outlaw programmer wrote:
None of the rest of the stuff you're talking about matters. At all.
It does to me; you are not the only reader. And in the grand scheme of things, none of it matters :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Since the package store allows you to store your packages in either the File System or the Database I suspect the question you you actually want an answer for is whether to store your packages in the File System or the Database. The package store itself is more of a management solution. Pros for Database: One point of backup, and probably easier to handle in a multiple instance system. Easier to use from any server or client. Pros for File System: Easier to export/import between systems. Just copy the files. I suspect it's mostly depends on how you work and develop your packages, I would also guess there are security concerns to think about.
Wrong is evil and must be defeated. - Jeff Ello
