December 17, 2007 at 10:33 am
Hi,
Wanted some expert advice on what is the best practise in storing the SSIS packages, storing it in msbd vs storing them on the file system. The scenario is that we built a lot of SSIS packages and I have a SQL Server 2005 standard edition. I deployed them to the server but when trying to run it from the server couple of my tasks failed. It could be due to the fact that Standard edition do not support advanced ETL operations. But honestly I am not sure as to what exactly do they mean by advanced ETL operations. The other option would be to store the packages on the file system and to run them from there.
Please advice
Vinu
December 17, 2007 at 11:33 am
The limitations of SSIS standard edition exist regardless of wether or not the packages are stored on the file system vs. msdb. Refer to http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Tommy
Follow @sqlscribeDecember 18, 2007 at 6:19 am
It might also be a security issue; especially if you calling it from a job.
I would first test this by logging into SSIS via SSMS and trying to execute the package directly from there. If this runs then it is an issue with the job calling the SSIS Package. If not this may at least give you some error messages.
In terms of the "Advanced ETL" option that will not run on SQL Standard they are:
-Data Mining Training Destination
-Data Mining Query Component
-Fuzzy Grouping
-Fuzzy Lookup
-Term Extraction
-Term Lookup
You can run these with either Development or Enterprise. SSIS is not available at all in Express and only the Wizard is in Workgroup.
-Mike
December 18, 2007 at 10:01 am
Also remember, there is a difference between the 32bit and 64bit environment. Since my database is 64bit and my ETL box is in a 32bit environment, I store them as file system. The best advise is to ensure that you know your complete environment before you start development. That way the little gotcha's won't getcha.
Happy programming
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
December 18, 2007 at 10:57 am
Hey Marvin,
For SQL Server and SSIS is doesn't matter if all the boxes aren't the same (32 vs 64 bit). The only time you need to worry about this for the various drivers for the data connections as some are not 64 bit compatible. For example, MS Access and Excel do not have 64 bit drivers but you can still deploy the package to a 64 bit server. you just need to call the package and specify a 32bit driver.
Otherwise it doesn't matter how you store them; be it SSIS Package Store, SQL Server or File System.
-Mike
December 18, 2007 at 11:08 am
Mike
Thanks for the clarification.
Marvin Dillard
Senior Consultant
Claraview Inc
January 22, 2008 at 2:10 pm
The execution of a package isn't affected by where you store the package, but if you want to save the package as part of a solution, either to group it with other packages or to use source control, you have to save it to the file system.
Maintaining the package is usually easier if you store it in the file system, deployment may be easier if you store them in msdb.
January 23, 2008 at 3:09 am
in my case, i decided to split hairs:
if package to run as an automated job or (if you get it everything right including the people outside IT) that package will no longer require modification...
then msdb
if package is to be modified before running daily or monthly to point to which file or entries need to be adjusted or a number of different reasons that expert SSIS programming can help (and i am no expert yet at this time)
then file system
---
frustating isnt it...it seems some dbas and programmers had so many wars so microsoft had to this schizophrenia.
quite sad, really, i am both the administrator, database designer, programmer and software architect so when i was building a system i could go high low far wide near narrow...really match my plan changes from one end to another end. of course the enterprise ones would say, you must be a SMB..i say yes...contentedly.
August 21, 2008 at 8:37 am
Marvin,
Reading this old email does make alot sense to me right now. I'm running on a 64 bit server and using File system for my SSIS packages. But one thing I need your opinion is that we are on a cluster environment 'A' and 'B' node. If 'A' goes down then 'B' comes up.
Right now my packages are saved on the local drive by default. Should I make it a practice everytime I save a package to on the 'A' mode, go ahead make a copy to the 'B' mode? Do you have another suggestion?
Can SSIS packages be saved on shared SAN drive and runned there?
Anything helps.
Thanks!
Javier
August 21, 2008 at 8:54 am
Create a disk resource and save the packages there.
The SSIS package can be executed from anywhere where the service can read them. i.e. A drive, San disk, online storage, SUB Stick... you get the idea 🙂
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 21, 2008 at 8:57 am
Why don't you save the package in the SSIS Package Store and then you dont need to worry about disks?
-Mike
August 21, 2008 at 8:57 am
Thanks Crispin!
I will test it out.
Javier
August 21, 2008 at 8:59 am
Saving packages to MSDB creates far more hassle than it's worth.
Editing, deploying, debugging etc etc.
Keeping them on disk is far simplier. You can keep packages, logs, config files all together.
I have yet heard a valid argument for not keeping them on disk.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 21, 2008 at 12:22 pm
I definately about storage on the file system vs MSDB. I think the file system is much cleaner and I actually have some SSIS packages that has nothing to do with a database ie reading from excel files, text files in the such, so why have database overhead slowing you down?
Each situation is different and it does come down to what the developer is comfortable with.
Marvin Dillard
Senior Consultant
Claraview Inc
August 21, 2008 at 10:35 pm
Your are definately right. Every situation is different. There are times when I use MSDB for data transformation between servers and there are times when data extractions are written to Excel files which is use File system.
You can't limit yourself just with one. We need to make the right choice to get the job done right.
Javier
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply