Is SSIS really that bad?

Question

Is SSIS really that bad?

Viewing 15 posts - 31 through 45 (of 45 total)

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

MVDBA (Mike Vessey) wrote:

SSIS is not as bad as people make out, purely because people don't know how to use it. I think DTS was better (but im an old guy) -

Spot on. Ironically, that's why I say that SSIS is so bad (it's usually not). With only minor exceptions, SSIS is fairly easy to use and that's why I don't understand the messes that many people make with it, which is the real reason I say it's bad. A big double whammy is that not only do a lot of people not know how to use SSIS properly, but they also don't know much about databases or T-SQL. It's always fascinating for me to see people writing script tasks to do things that are actually quite easy (usually easier than in a script in a different language) to do in a stored procedure using T-SQL.

With that, I'll also state that I don't generally use SSIS. In fact, I don't use the product directly. About the only thing I use it for is as a process control system when I need to control asynchronous stored procedure runs in parallel and other flow controls that SSIS does make really easy. I don't ever use SSIS as an actual ETL tool beyond any flow control needs I may have. I certainly don't use it directly to load files to tables. I do all of that in stored procedures using many of the straight forward and nasty fast tools available in T-SQL.

I've also been hired many times to improve ETL processes and the first thing I normally do is get rid of the related SSIS packages because of the atrocities heaped upon the system by the people that wrote those packages. I'm telling you that to emphasize that, despite my normal denunciation of SSIS, it's not actually the tool that's the problem. It's the people using the tool.

Here's a classic drawing of what I'm talking about... the hammer is the representation for SSIS... the nails are what users do to it...

diy-hammer-nail-do_it_yourself-i-diy_project-mkan367_low

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

x wrote:

If I can ever get SSIS to work, I'm hoping to start using it, I used to love bcp, other forms of bulk loads etc, but our systems got reorganized so I don't have access to the windows job scheduler or xp_cmdshell anymore which use to be my goto tools, so hoping to expand my horizons and see what other tools folks might be happier with at work here.
Unfortunately, made the studio 2019 mistake and can't get SSIS to work right now.

I saw your tail of woe on a different thread. Man, they made a mess out of things. I really appreciate your post on that so that because it's going to keep us from going through the same thing at work.

Heh... shifting gears a bit, I hate it when people pull the carpet out from under people by making it so that they can no longer use well established tools that have been working (and working safely) because of someones "reorganization" ideas. If you need some help denouncing the mistake of not allowing the use of xp_CmdShell, let me know. I have an hour long presentation based on the scientific method about it and two incredibly tests to support it all where I challenge auditors and other naysayers to make them fail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

Jeff Moden wrote:

I've also been hired many times to improve ETL processes and the first thing I normally do is get rid of the related SSIS packages because of the atrocities heaped upon the system by the people that wrote those packages.

I nearly fell off my chair laughing at that- we've had to support a SQL 2000 server for DTS because the "transformation wizard" made the DTS files binaries stored in MSDB and completely unusable - nobody bothered to check out the "export package" feature and open it up in SSDT (I had to explain that one too)

I've had to rewrite SSIS , BCP, BULK INSERT and openquery objects just to get rid of the "ATROCITIES"

thankfully all of mine are delimited files where it may change occasionally (extra fields etc) and we need to make a non-technical team self sustaining rather than coming to the DBA every 5 minutes... so SSIS is perfect as it is very visual

This reply was modified 5 years ago by MVDBA (Mike Vessey).

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

x wrote:

If I can ever get SSIS to work, I'm hoping to start using it, I used to love bcp, other forms of bulk loads etc, but our systems got reorganized so I don't have access to the windows job scheduler or xp_cmdshell anymore which use to be my goto tools, so hoping to expand my horizons and see what other tools folks might be happier with at work here.
Unfortunately, made the studio 2019 mistake and can't get SSIS to work right now.

I'm curious - I've never had SSIS not work - what issues are you having?

MVDBA

x SSC-Insane Points: 23660 More actions · Answer 5

Jeff Moden wrote:

x wrote:
If I can ever get SSIS to work, I'm hoping to start using it, I used to love bcp, other forms of bulk loads etc, but our systems got reorganized so I don't have access to the windows job scheduler or xp_cmdshell anymore which use to be my goto tools, so hoping to expand my horizons and see what other tools folks might be happier with at work here.
Unfortunately, made the studio 2019 mistake and can't get SSIS to work right now.
I saw your tail of woe on a different thread. Man, they made a mess out of things. I really appreciate your post on that so that because it's going to keep us from going through the same thing at work.
Heh... shifting gears a bit, I hate it when people pull the carpet out from under people by making it so that they can no longer use well established tools that have been working (and working safely) because of someones "reorganization" ideas. If you need some help denouncing the mistake of not allowing the use of xp_CmdShell, let me know. I have an hour long presentation based on the scientific method about it and two incredibly tests to support it all where I challenge auditors and other naysayers to make them fail.

I believe you, but science doesn't always triumph over superstition. I'd love to see the entire topic get the treatment it deserves. However, to even begin to describe the changes I've referenced that caused my own loss and rearrangement of toolsets, I'd have to change my username several times more, but yeah xp_cmdshell is not coming back.

Thom A SSC Guru Points: 99130 More actions · Answer 6

MVDBA (Mike Vessey) wrote:

I'm curious - I've never had SSIS not work - what issues are you having?

SSIS on VS2019 is, currently, a mess. It's still in preview, and I really don't recommend it. They moved away from SSAS/RS/IS being packaged into SSDT in 2019, and they are now separate extensions, and the SSIS one is just... Well, it leaves a bad taste in your mouth right now. By Preview, they really do mean "Buggy and unfinished".

Stick to VS 2017 and SSDT.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

I use the 2017 tools (vs and ssdt) but I have just bumped up to sql management studio 18.3.1 which is "not what I expected" - especially since it has to run in parallel with version 17 - no discernible new features and lots of bugs

plus a half hour downtime while I install it... and then reinstall Redgate tools - at least I got some coffee that day

MVDBA

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 8

We use SSIS a lot for our ETL. Recommended is VS2017 + data tools , VS 2019 is extremely buggy at the moment.

Pro's:

Flowchart, logging, catalog, almost no code required but allows custom scripts

With biml, you can generate SSIS packages from metadata.

Con's:

Not suited for Excel. Too many a time there is an extra column, row that crashes the import. Merely reporting Validation failed

Not suited to report validation errors to end user

our scheduling tool currently doesn't capture all possible ssis return codes

runs on expensive cores

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

by the way - has anyone twigged that permission management for SSIS is a pile of garbage?

MVDBA

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 10

Jo Pattyn wrote:

Not suited for Excel. Too many a time there is an extra column, row that crashes the import. Merely reporting Validation failed

In general I don't consider excel a suitable source for any ETL period.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 11

I've found working with Excel as a source for SSIS works a little better when you specify Data Access Mode = "SQL command" and when the first row has column names in it. Then you can write it like a query:

SELECT [myfirstcol],[mysecondcol],[mythirdcol]
  FROM [Sheet1$]
  WHERE [myfirstcol] IS NOT NULL

I don't like spreadsheets as a source either, but often that's the easiest tool business people have to give data to IT. What is really horrible is spreadsheets as a destination in SSIS. 😛

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

MVDBA (Mike Vessey) wrote:

by the way - has anyone twigged that permission management for SSIS is a pile of garbage?

Not in that vein but I have said that it's an unnecessary expansion of "surface area" and expansion of users that frequently don't know what they're doing and so had to resort to such a tool, mostly to help expound on the fallacy and mistake of thinking that disabling xp_CmdShell provides anything more than a trivial 3ms half-height speedbump to attackers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dmbaker SSCertifiable Points: 5139 More actions · Answer 13

I use SSIS quite a bit where I work and in general it's a pretty useful tool. What I've found though, is that it tends to work best when you use SSIS to push data from one place to the other, then use it to run/automate code (e.g. stored procs, views) that you've got in your database, rather than trying to do all the transformation work within SSIS (in other words, make the database do the work, coordinating it with SSIS). Once you start getting complicated transformations all wound up in SSIS it can get quite difficult to follow what's going on. Yes, it presents a "flowchart" of sorts, but if you don't take the time to name your object and annotate your flow (something it seems folks rarely do), someone else who has to deal with your code later is going to have a hard time of it.

One very serious shortcoming with SSIS has to do with source code control. Because of the way the files for SSIS packages are stored it is virtually impossible to do a before/after compare of your code. SSIS stores files as XML, and script code gets stored in a binary blob. Good luck trying to figure out what changed between versions.

Deployment is sometimes a pain too, if you're not careful to target the correct version (specifically, the version that's deployed on your server) and heaven forbid if you use the "wrong" version of the deployment wizard to deploy your code to the server...say hello to weird, incomprehensible error messages that seem to have nothing to do with the actual problem (which is a problem in general...lots of sometime weird error messages that give you no clue as to what the problem really is). Trying to export data to Excel is a nightmare too.

So love/hate I guess. It's not really that bad, until it is, at which point you're probably trying to do something that maybe you shouldn't be doing with SSIS anyway (or you should rethink it to use SSIS more effectively or maybe consider something else).

James Collett SSC Enthusiast Points: 160 More actions · Answer 14

SSIS is not "really that bad". It is worse.

The error messages are often incomplete or misleading. Or non-existent.

Thom A SSC Guru Points: 99130 More actions · Answer 15

James Collett wrote:

SSIS is not "really that bad". It is worse.
The error messages are often incomplete or misleading. Or non-existent.

I find this untrue, if i am honest. The errors messages often are quite clear and point you exactly where you need to look; SSISDB with its logging made this even easier.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk