Fri 27 Jul 2007
Over here, Oren posted a list of 15 issues with SSIS. Some are founded, most are not. Read on for more:
Below are each of Oren’s points with my response:
Bad Errors:
You have to understand though, that this isn’t .Net. SSIS has many components/engines at work that obtaining the correct error isn’t always at the heart of the SSIS engine. It could be a database error. It could be an ADO error. Whatever it may be, I agree, some are cryptic, but I’ve generally been able to diagnose my errors. And if there is an error I don’t know about, I contact the community and finally Microsoft through product support.
Random Errors:
The fact these are random should banish this item from your “SSIS 15 Faults” list.
Keeping track of what it shouldn’t:
Never had this happen. NEVER. NEVER, NEVER, NEVER, NEVER. Did I make my point? Come over to the forums for help if this is happening and we can help troubleshoot.
Sorry excuse for deployment:
I don’t understand any of the points made here. Deploying to a server has never been a problem. It is in the manual that metadata cannot change between databases/tables. So if you’re moving to an environment that has its metadata different than another, you need to reconcile that first. Also, you aren’t using “select * from table” or selecting tables in the drop down box in the OLE DB sources are you? NEVER do that. Always select the specific columns you need (even if it’s all of them) manually so that DBAs can add fields to the table without causing metadata changes.
Security? Who needs that:
An SSIS package doesn’t need sysadmin rights to execute. Period.
UI Formatting instructions:
Ah, well, what would you say if the SSIS dev team decided to make SSIS packages binary? THEN WHAT? At least you have an XML file that can be parsed.
No thought about version control:
Agreed. Version control is flaky.
Bad configuration scheme:
I don’t understand this. Please elaborate.
Random configuration scheme:
B.S. - I beg to differ.
Bad UI:
Yes! The UI needs improvement.
Lack of extensibility:
Really? Darren, Jay care to comment? There are plenty of API guys that know what they’re doing and can help you out if you just ask.
Bad interoperability:
Is your Oracle example an SSIS problem, or the Oracle driver’s problem?????????
Busy work:
Yep, agree.
Hard to debug:
Within reason, yes, this is true. You now know about using the watch box.
The missing basics:
UPSERT? Really? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1
July 27th, 2007 at 1:20 pm
Phil,
I think Oren could legitamtely complain if he had to contact MSFT Product Support to get an error message explained
I take your point though. Cryptic erros are usually raised by “things” external to the package - all SSIS is doing is surfacing them for you.
So yes, this point about error messages can be argued either way and I am somewhere in the middle.
-Jamie
July 29th, 2007 at 12:19 am
[...] see that Phil Brammer beat me to a rebuttal. Go Phil! Posted: Sunday, July 29, 2007 1:18 AM by [...]
July 29th, 2007 at 12:49 am
[...] see that Phil Brammer beat me to a rebuttal. Go [...]
August 13th, 2007 at 10:46 am
My thoughts
>> Bad Errors:
Yeah, SSIS does report errors horribly badly at times. The most annoying seem to be
ones caused by deletion of a component that triggers an downstream problem in a data
flow.
>> Keeping track of what it shouldn’t:
>> Never had this happen. NEVER.
I have. SSIS sometimes seems to retain data about connection managers that have been
deleted from the package. They aren’t there, and can’t be removed, short or directly
editing the XML. Script components are another case. I find it truly ridiculous that
a defect in the cached compiled CDATA block can prevent a script component from being
edited; the obvious correct behavior, IMHO, is to offer the option to rebuild from the
cached source.
>> UI Formatting instructions:
>> At least you have an XML file that can be parsed.
Parsed with enormous difficulty. The worst problem caused by combining the UI formatting
with the executable content is that is is impossible to effectively diff versions. Every
attempt to do so results in thousands of changes that are effectively “this control moved
one pixel over.”
>> Hard to debug:
>> Within reason, yes, this is true. You now know about using the watch box.
In addition, it’s very difficult to profile preformance and find bottlenecks. Only after
adding LOTS of additional script components to log data were we able to uncover the
causes of some problems and work around them. In some cases this resulted in a 40-fold
speed increase over the defaults in SSIS.
Two more things I’d like to add to the defect list:
Compile doesn’t mean compile everything. There is apparently no way to force a recompile
on script components, other than open them all in the editor, save, and exit — really tedious.
Continuous validation! I understand why SSIS was coded to validated everything every time
at run time; I don’t agree with it, but I understand it. But why can’t developers set the
dev environment to validate only when I say to compile!
October 3rd, 2008 at 7:35 am
Haha, go Philibuster go! If just about every person I’ve ever talked to who has had massive previous experience with doing these kinds of tasks, finds this program to be the biggest turd ever released, it means they are all incompetents. Every one of them. It is quite reasonable to expect a worker to spend several weeks reading documentation to do the most trivial tasks that previously could be done by an untrained worker in half an hour with DTS. This doesn’t mean the SSIS program is badly designed, by people who have no REAL WORLD experience. Rather, it means everyone is stupid and lazy except Microsoft shills.
The joke here is that I really learned how to use C# a lot better, because it was out of the question to waste time on that joke of SSIS to automate things. Using MS Studio - can learn on the job. Using SQL Management studio - can learn on the job. Using SSIS - take a few months off work and play around with a morbidly complex and buggy program. In my workplace there are a LOT of flat files that need to be parsed and imported into SQL tables on a regular basis, and I’m halfway through writing my OWN framework to do all that, because SSIS is so flippin awful.