Are my issues with SSIS justified?

Question

I've only been using SSIS briefly, but I find that my complaints are numerous. Here are my current issues:

1. In order for a package to store a password, you need to encrypt it. Even if the package is part of a larger solution, you need to supply a password anytime to open any of the encrypted packages. Why can't you just encrypt the whole solution with one password? I have a solution with 10 encrypted packages. When I hit "Build", I have to enter 10 passwords.
2. Encrypting credentials is great. Deploying an encrypted package to the server, supplying your password, testing it successfully, scheduling it, and then having it fail during the schedule because it can't decrypt itself SUCKS. It seems to randomly do this, and I have redeployed a given package several times before it is actually able to decrypt the package credentials successfully during a scheduled job.
3. Windows authentication only? Maybe this is a security feature, but it makes it a real pain in the butt to remotely manage the server. It basically forces me to use remote desktop. Does it really matter that I can't access SSIS when I have access directly to the DB Engine???
4. DTS Support. DTS was pretty ugly, but it worked, and was pretty straightforward. Why didn't they provide the DTS 2000 package designer WITH SSIS??? Now I need to go download it and install it with admin privileges.
5. UPSERTS??? I replicate some data to an external database, and upserting to that database is SUCH A PAIN. Why isn't this functionality built in? Why can't I just say "This is the key column. Update if exists, create if it doesn't".

Are these valid issues, or am I just to new to the product to know how to do things the right way?

Do others have the same issues, or other issues?

Are there easy alternatives to using SSIS?

Answer 1

We ran into many of the same issues, especially #5, so I agree these are valid. In general, I found SSIS to be a massive pain in the ass to work with.

Answer 2

Check out Package Configuration files for some of the security issues.

Answer 3

In my system it was overall easier to just develop data loads in C#. The loads are rock solid and do not change unless we want them to change, so we don't spend any more time after we are done with development.

Answer 4

These links from @SQLServerSleuth might shed some light on the situation - a back and forth re: SSIS in 2005. Are you on SQL 2008, or still working with SQL 2005? This picture changed a bit in 2008.

http://bit.ly/G21td http://bit.ly/sbC2B http://bit.ly/NR8XE http://bit.ly/167mjn

Answer 5

For 1, 2, I use package configurations.

For 5 you can use a slowly changing dimension task or the third-party table difference component. I personally prefer to load to a staging table and code the UPSERT in SQL.

I've been using SSIS fairly intesively on a DW project for the last 2 years and I find it to have a few quirks but it is far more powerful than DTS.

Answer 6

Do you actually need the encryption on each package? You can say no encryption storage if you aren't storing an ftp or other authentication passwords. Configuration files are also a good idea. I recommend www.pragmaticworks.com/products/Business-Intelligence/BIxpress/ BIExpress as it will create all the config files for you, log the crap out of your packages and provide awesome blow your socks off graphical reporting for next to nothing cost wise...

Answer 7

let me preface this by saying that SSIS sucks. it's a pain to work with, manage, and develop. While there are tools that make things better, these features should have been included from the start. let me also say, that I haven't found (and don't believe there currently exists) a better tool for scalable high performance data loads than SSIS.

1,2: set the package to "Don't Save Sensitive", and use either configurations, or "Set Values" inside whatever execution context you are using.

3: agreed, partially. browsing the package store would be nice through sql auth, but executing the package should absolutely not be allowed (under what context do you execute?) You can always execute through the job.

4: not related to SSIS besides, DTS is deprecated, and in most ways, considerably less flexible and harder to manage than even SSIS.

5: Upserts are admittedly trickier than they could be, but if done right, it can work flawlessly: either use a lookup to determine whether you need to insert or update, and define your logic accordingly.

side note: seriously consider setting up a package template. if done right, you can alleviate many of these concerns from the start. I may need to publicly post my package template at some point.