I've only been using SSIS briefly, but I find that my complaints are numerous. Here are my current issues:
- 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.
- 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.
- 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???
- 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.
- 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?
Is SSIS worth learning?
SSIS is a great tool, but you have to grasp underlying concepts first. It allows you to focus on the ETL process, instead of programming and debugging your code. There is a learning curve. Take a look at Microsoft Project Real examples and see what would be foreach loop equivalent for something like that.
When should I use SSIS package?
The primary use for SSIS is data warehousing as the product features a fast and flexible tool for data extraction, transformation, and loading (ETL).). The tool may also be used to automate maintenance of SQL Server databases, update multidimensional cube data, and perform other functions.
The following 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.
- SSIS' 15 Faults
- SSIS: The backlash (1)
- SSIS: The backlash (2)
- SSIS: A response from Microsoft to some growing criticism
Menu
866
