SSIS Tries So Hard To Make You Hate It

<< Strive for 4-5 failures per month! | Home | Nant Task for Creating IIS Application Mappings >>

SSIS Tries So Hard To Make You Hate It

posted @ Wednesday, April 11, 2007 9:49 PM

For a while last year, I thought I was the only one in the world using SQL Server 2005's new SQL Server Integration Services (SSIS) and I was feeling like there was very good reason for this. SSIS tries REALLY hard to make you hate it. The development experience in Visual Studio is god awful. Strange, vague error messages, counter-intuitive set-up, implementation, and configuration procedures, and about the worst possible experience you can imagine if you try to check your SSIS packages into source control and have more than one developer work on them.

The reason I felt so alone was the extremely limited documentation and lack of user posts I could find about *anything* SSIS related (granted, this began last year when they were very new). Well, I still really dislike SSIS, but I don't feel as alone. Even the great Ayende is feeling the pain a bit.

Being that SSIS and their config files are just XML documents, one would think sharing the packages via source control would be a fairly trivial matter. Well one of the dumbest/most frustrating things about SSIS is that the config files (.dtsConfig) contain NO formatting. Yep, one big XML string on ONE line. Well-formed XML, I admit, but unreadable/uneditable. And considering you need to edit these things regularly, prepare for pain.

Additionally, the fact that the entire file is on a single line makes version control (especially with my favorite, Subversion) a real nightmare, as they compare line by line when merging changes.

There is a lot of room for improvement in SSIS, especially the development environment and experience.

Update: This post seems to be incredibly popular - apparently many people are googling "SSIS" and "Hate" :) So, I thought I'd point to some of my other SSIS Frustration Posts:

Ayende Nails SSIS' Faults
Reason 94,532 To Hate SSIS
And, an alternative to SSIS: Ayende Takes On SSIS

Comments

MLKMLK
Posted on: 9/19/2007 11:31 PM
# re: SSIS Tries So Hard To Make You Hate It
I totally agree. I have to type this up, so it can be Googled everywhere. SSIS sucks. Overall, I see that Microsoft wants to great things with this product with good intention. But the Microsoft QA department SUCKS BIG TIME. How can you let this piece of shit passed QA? I bet the MBA guys at Microsoft put a gun on you head to pass this piece of shit. All these little bugs(features) here and there just drive me crazy. I know very well about using any 1.0 product from Microsoft. But once you use SQL 2005, you don't have a choice, but to use this 1.0 Business Intelligence Studio for SSIS. @$R$()#*$!
BK
Posted on: 10/2/2007 9:41 AM
# re: SSIS Tries So Hard To Make You Hate It
I thought I was the only one that HATES this software. There are so many bugs, and the overall experience is so user-unfriendly.

Last night I had the joy of finding out the Transfer Database task does not copy primary keys, foreign keys, or indexes. Now I'm re-writing 8 packages to use the Transfer SQL Server Objects task instead.

Just another warning sign that Microsoft has jumped the shark. After this experience, the question isn't if I'm going to SQL 2008, it's if I'm going back to SQL 2000.
Grim Repear
Posted on: 4/1/2008 9:54 AM
# re: SSIS Tries So Hard To Make You Hate It
SSIS is the worst program I have ever seen in my life! How badly designed an interface can you GET? When it takes a day to make a program to import a simple flat file, there is something very wrong, and I have been doing these sort of tasks for 25 years!
Frustrated
Posted on: 6/3/2008 12:28 PM
# re: SSIS Tries So Hard To Make You Hate It
Oh Lord help me! ActiveX scripting is a pain to setup and unreliable too... WTF. why did they mess it up so much? I liked the Enterprise manager interface so much better ... very light on resources too.
SSIS sucks
Dave
Posted on: 6/19/2008 4:56 PM
# re: SSIS Tries So Hard To Make You Hate It
Oh yes, SSIS sucks big time. How 'bout this for some suckage:

1. Tiny text entry points for conditional split conditions (and many other places). I have to key my conditions into a text editor and copy/past them into this POS.

2. The most freaking common things to do in data land are like getting a root canal in SSIS, starting with importing an Excel SS into a non-unicode table. What, MS does not know that my table is not unicode and that Excel is? I'll take implicit conversion any day that I AM IN A BIG FREAKING HURRY. And any anal retentive C# (otherwise known as fake C++) programmer that says otherwise can...

3. Today's suckage of the moment: I'm doing an incremental load of dozens of tables with hundreds of fields whose structure I am not familiar with, and the conditional split is pissing and moaning about conditions that evaluate to NULL, with no way to globally handle the situation. SSIS sucks to an unimagineable height on this one.

What, does MS not know how common incremental loads are? I should just be able to select my source and dest and press the @#$%#@ button. Thanks for nothing MS weiners.
MS Shill
Posted on: 6/25/2008 12:48 PM
# re: SSIS Tries So Hard To Make You Hate It
There is nothing wrong with SSIS. This just proves that microsoft programmers are the best and you guys who hate it are linux fanboys.
Knut Bohn
Posted on: 6/30/2008 2:58 PM
# re: SSIS Tries So Hard To Make You Hate It
I finally succumbed to the ever mounting urge to Google "SSIS piece of s*** and I just have to get this off my chest... I can't even begin to list all the issues where SSIS is a complete dud; from the simplest tasks of transferring/copying to the unbelievably complex implementation, winning combination of ubiquitous & impenetrable security. MS; please replace this sorry excuse of a tool!!
(And I don't even feel better, cause I still don't understand what this transfer server objects task is whining about - in the task properties I alerady told you: DO NOT COPY ANYTHING I DIDN'T TELL YOU TO COPY!!!) Good grief.
Knut Bohn
Posted on: 6/30/2008 3:09 PM
# re: SSIS Tries So Hard To Make You Hate It
Followup after more successful google:
"I'm not able to copy objects with the 'transfer SQL server object Task'. I opened a call as a Microsoft Premier-support customer. No chance to run a package without errors. Microsoft said, they know the errors but they will be corrected in SQL2k8.
"
MS Shill: I don't know what you've done with your SSIS, but mine - there's practically nothing right with it. And I'm a BIG fan of sotware that works - regardless of who made it...
Glen D'Abate
Posted on: 7/2/2008 11:28 AM
# re: SSIS Tries So Hard To Make You Hate It
SSIS is perhaps the most poorly conceived software upgrade Microsoft has ever release. I've asked staff to perform a few basic tasks that an untrained program could typically complete in less than 30 minutes using DTS. The same basic tasks (e.g., exporting a table to a flat file and reordering columns) take our new employees an average of more than 10 times the time to accomplish (when they can complete the task at all!). Microsofts in their never ending quest to bloat software packages with "features" has missed the mark with SSIS. They need to get out of their ivory towers and find out how the real world uses their products before releasing new versions.
Mark Phillips
Posted on: 7/21/2008 3:13 AM
# re: SSIS Tries So Hard To Make You Hate It
I have been working with SSIS around 6 months now and its not as bad as you make out. Its a vast improvement from DTS. Yes I agree there are nuances in the development environment and debugging but I think there is a bit of a learning curve and it is not something you can start using immediately - it takes some time to learn. At least there is now a lot more discussion and real world examples out there now.
Franklin Edwards
Posted on: 8/14/2008 9:21 PM
# re: SSIS Tries So Hard To Make You Hate It
Buggy, unstable, user unfriendly. A huge disappointment. I needed to add a gig of ram just to be able to use the client tools. If this is what 5 years of work produces then I don't want to see what is offered in 2008.
Analyze This
Posted on: 8/27/2008 11:14 AM
# re: SSIS Tries So Hard To Make You Hate It
It continues to amaze me that those who don't take time to read the directions complain when the product doesn't work.
Brian Donahue
Posted on: 10/1/2008 10:05 PM
# re: SSIS Tries So Hard To Make You Hate It
@Analyze This,

Care to elaborate and/or point me to the directions that I (and so many others) am missing? I have read books, online docs, blog posts, and still have experienced a ton of pain with SSIS and I'm nowhere near alone in this. I'm sure there are people out there using it successfully, but there is an abundance of anecdotal evidence to show that that's often not the case.
Rob Barbato
Posted on: 10/16/2008 12:50 PM
# re: SSIS Tries So Hard To Make You Hate It
Oh come on -- it's at least as good as Vista. The best software Micorsoft can develop with its "outsourced" dollars.
Matt Freeman
Posted on: 11/4/2008 5:56 AM
# re: SSIS Tries So Hard To Make You Hate It
SSIS is a pile of ..... I just copied an SSIS project from one machine to another (on the other machine I need to change the hostname of one of the sources databases due to VPN etc..), after waiting 20 minutes for validation to gracefully fail I couldnt reconnect the datasource, it complains about keys etc.. now.. Switching to offline mode won't help due not being able to connect up to the other server. Its so fragile and its look like only minor GUI improvements have been made for SQL 2008. It should never have past QA.
Isaac Johnson
Posted on: 11/10/2008 12:29 PM
# re: SSIS Tries So Hard To Make You Hate It
I'm not going to lie; I have not thoroughly exhausted many resources available to me to fully learn SSIS. But I also must say that I was very successful setting up advanced DTS packages under SQL 2000 and they would run for YEARS. Portability? No problem, just take the .dts file and import it to the new SQL server.

HERE???? SSIS requires all types of edits. Heck, I just picked up SSIS today after reading about it just to see if I can accomplish a simple data migration from one table to the other and the so-called "Connection Managers" require configurations and associations to the Nth degree. Just getting my data flow task to play nicely with the configured connection manager was a curious chore. This product is laughable.
Ben
Posted on: 11/19/2008 11:59 AM
# re: SSIS Tries So Hard To Make You Hate It
SSIS blows a donkey and that's the final word. I have read the books and online docs, done the tutorials, etc, etc, etc, and all it gets me is repetitive and frustrating errors. What am I doing you ask? I'm trying to import a table from our production database, running on some crap old C/ISAM tables accessed through ODBC, to a SQL 2005 database. It simply doesn't work. I find now that the task may fail if my source data has NULL values for certain fields. WTF!? SQL 2000 I could whip the bastard into shape with the freakin' DTS WIZARD in less than 5 minutes. Unbelievable. Everything Microsoft has touched lately has turned to obfuscated bullshit. F---in' retards and their "improvements." If I have to read one more article written by some Microsoft ass pirate that says "DTS wasn't good... SSIS is a REAL ETL tool" I'm gonna puke.
Dave
Posted on: 12/10/2008 6:49 PM
# re: SSIS Tries So Hard To Make You Hate It
SSIS Blows goats. Does anyone know if it has been improved in MSSQL 2008? I'm debating about making the permanent switch to MySQL.
Gary
Posted on: 12/15/2008 1:48 PM
# re: SSIS Tries So Hard To Make You Hate It
This is awful!!! I have to load some csv files into a table and it's ridiculous how hard it is. Does anyone know if the SSIS in 2008 is any better?
Posted on: 12/23/2008 8:40 AM
# SSIS continues to SUCK &laquo; Yet another blog again
SSIS continues to SUCK &laquo; Yet another blog again
George
Posted on: 12/23/2008 5:34 PM
# re: SSIS Tries So Hard To Make You Hate It
I've got a solid 3 years with SSIS having written at least a couple hundred packages and have been using SSIS 2008 for the last 6 months. I can develop packages very quick, some in just a few hours. It has some real performance advantages over using plain old SQL for CERTAIN kinds of jobs (usually where many tables are joined) but I have to admit that the posts on this thread are spot on.

The nuances never stop! It seems like no matter how many times I do something similar, I always experience some weird behavior or bug that makes me have to work around it. For example, recently I was implementing a large dataflow with an OLEDB source using parameter substition (you know where you use the "?" question mark). The OLEDB source would take an hour to start producing data for the rest of the dataflow after I clicked go. It's a simple query with a ? for a single condition in the where clause. To troubleshoot, I copy paste the same query to a query window (replacing the "?" with a value) and I get the whole dataset in less than 5 seconds. I hard coded the value in the OLEDB source, removing the "?", and guess what, I get query results in less than 5 seconds. I discovered that if I use an SQL expression, substituting the variable into the expression, I get results in seconds. For some reason, for this particular package, it does not like parameter substition. I have several other pacakges where I didn't need to do this. The question is WHY? Like everyone else I have a job to get done and unfortunately can't spend lots of time debugging a product my client bought (that is supposed to have been tested).

I just discovered that placing a UNION ALL after a LOOKUP in the dataflow creates a permanent "package contains duplicate column...blah blah blah." I had an ETL that could match on anyone of multiple keys so I was trying to lookup one at a time, if it didn't match, it would go to next match criteria. I was hoping to UNION ALL the matches but guess what, you can't.

By far the worst is the merge join. Connect and dataflow with 100's of columns to a merge join. Whenever you need to modify the merge join, it will take several minutes and all your CPU (and there are 4 CPUs on the dev box I use!!!) to close the merge join after you click ok. I have a package with many merge joins. So guess what. when I have to add a column to the dataflow, it becomes a several hour chore of opening the next merge join, clicking on the column to pass it through, and then clicking to close the merge join. It's horrible for productivity. What's worse is this problem existed in 2005 and they did not fix it for 2008.

Maybe it will be fixed in SQL 2011 or whatever one is next. But many times I have contemplated going back to plain old stored procs and SQL code. At least I know that works 100% of the time and I won't waste a day trying to work around some obscure bug.
Helix
Posted on: 1/5/2009 6:32 AM
# re: SSIS Tries So Hard To Make You Hate It
Ow how i hate SSIS building some packages right now but mi breaking my head on the non stop bugs and weird erros.. i hope this piece of shit software gets lost and forgotten!!
Rich
Posted on: 1/6/2009 2:46 AM
# re: SSIS Tries So Hard To Make You Hate It
Absolutely spot on. SSIS is so much more difficult to use that DTS was. What the hell was MS thinking?

Nothing is more frustrating than trying to debug "Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Description" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page." at 3 AM when you're trying to do a simple CSV export.
Bill Rong
Posted on: 2/12/2009 4:20 PM
# re: SSIS Tries So Hard To Make You Hate It
I was asked to create some SSIS package. What a NIGHTMARE! SSIS introduces so much f__king noises by locking your thinking process to the stupid GUI mess, definitely against the nature of human brain. The designer should be put to prison, for life!
mack
Posted on: 2/14/2009 11:18 PM
# re: SSIS Tries So Hard To Make You Hate It
Working on a simple(I thought) SSIS package to copy data from 1 db's table to a copy of that table in a test db. Needed to check if a varchar title already exists before inserting. After 2 hours of getting nowhere I'm frustrated and pissed off. Thinking I'm some sort of dolt. Googled 'ssis sucks' and lo and behold; seems I'm not alone. So why do so many employers use this crappy tool?
marsha
Posted on: 2/17/2009 11:37 AM
# re: SSIS Tries So Hard To Make You Hate It
The # of passwords to have a package run is incredible. The issue of EncryptSensitiveWithUserKey for the default is a disaster. It should not be microsofts job to see who is messing with your code. It takes levels of passwords to get the crap to run on the server why the heavy security???? Trying to import a simple spread sheet takes an act of congress.....thanks microsof!!!!!
robertt
Posted on: 2/17/2009 3:14 PM
# re: SSIS Tries So Hard To Make You Hate It
I have to agree. Nearly always the best option is simply to use scripts rather then hassle with ssis. The worst part about SSIS, is that you think you can do a lot with it only to find out that no in fact you cant do what you thought you would be able to. Merging is very difficult (the data must be sorted) and once you think you have it working. bam you find out about the column lineage must be the same. What is the point of a merge, if you have to have the same lineage for all the columns???
I would only recomend SSIS, if you have very similar database scheams and you are only doing the most simple of transforms.
Otherwise save yourself some trouble and start scripting.
defaultCharacter
Posted on: 3/17/2009 11:01 PM
# re: SSIS Tries So Hard To Make You Hate It
Wow, you guys are really freaking me out. I just started working with MS SQL Server 2008, and am doing the SSIS tutorials. Then I stumbled on this site and don't really know if I should try to use SSIS or not. :( I am starting a really large ETL project and all your comments are worrying me. I guess its good I ran into this blog. Arrrrgh.
Vern
Posted on: 3/23/2009 8:43 AM
# re: SSIS Tries So Hard To Make You Hate It
SSIS is like playing a video game that doesn't really work unless you have all of the cheat codes. I'm still trying to find all of the cheat codes...
Bob
Posted on: 4/25/2009 9:18 AM
# re: SSIS Tries So Hard To Make You Hate It
Microsoft completely missed the boat with SSIS. If you are tackling an ETL job, don't use it. Those who back SSIS say it's a giant leap forward over DTS because it is big and sophisticated enough to cope with large and complex transform scenarios. What they missed along the way is that programmers get paid for both results and expediency. The people who protest that it takes way too long to create/test/release an SSIS package are absolutely right. If I want to run in "implicit" mode, that should be my option. But the argument that SSIS's problems are the result of training issues and failing to read the documentation are utterly ridiculous. Scrap SSIS.
defaultCharacter
Posted on: 6/23/2009 12:43 PM
# re: SSIS Tries So Hard To Make You Hate It
I've been using SSIS 2008 since my March 2009 post (above). While there are a lot of complexities to deal with, so far I've been able to find ways around issues with google (yeah - it is kind of like playing a video game, as someone said above).

Though my applications probably aren't as complex as some of the previous posters, I haven't found anything that warrant the level of complaints I've read, but I'm not dealing with different databases or dozens of tables with hundreds of columns, as one person mentioned.

I've been working with one incoming data source and dispersal to basically one table after validation. I've been using a combination of SSIS tasks AND stored SQL procedures. Maybe the usage of the stored procedures is the wrong way of doing things, but I don't see how you can have that much control otherwise.

I'm new to the Microsoft platform, and I can tell you from many years of experience that SSIS is a lot easier than HAND-CODING. The SSIS designers obviously knew what they were doing when they put in little niceties such as row counters, logging capabilities, data watchers, etc., etc., etc.

BTW, I haven't seen any good Microsoft documentation on this product... does it even exist? I found a Wrox book that is okay. That book and google have saved my life.
KR
Posted on: 6/30/2009 3:43 PM
# re: SSIS Tries So Hard To Make You Hate It
Wow, I really didn't think googling SSIS Sucks would give me anything back but I did it because I am seriously about to lose it. All I want to do is export a CSV file. I have tried every combination of windows account and SQL account to get this POS to work. Somebody PLEASE tell me how to just script a stupid CSV file out of SQL. For crying out loud...
Onur
Posted on: 7/6/2009 12:36 PM
# re: SSIS Tries So Hard To Make You Hate It
Well,

Yes SSIS has many bugs, annoyances and unnecessary gestures, to-much-to-say-but-not-meaningful-error messages etc.

Someone could you please tell me aren't there any good points or developments in MS ETL Technology??

It is a shame for MS to sell not-properly-tested ETL tool to market, which could also taken as "forwarding the Beta-Testing to Customers" rather than taking it on self. But i suggest to look at the bright side and try to develop yourself about the wierd errors. Since I call it By-Error-Learning.

Yes i felt the same annoyances and anger on SSIS when i first start developing with it but later when you get used to it, it only tooks half time less than designing and later maintaining the DTS Packages.
But in the meantime i've learned many things while i was doing that.

Try to talk with SSIS by its own Language. You will get benefit more than you politically criticize it.

Best Regards
andrew h
Posted on: 8/7/2009 10:23 AM
# re: SSIS Tries So Hard To Make You Hate It
I completely agree, at first I was excited to try the new flashy MS tool, hoping it would improve on the unfinished DTS, but boy was I quickly disapointed! As mentioned many times, the simple csv import, which we used quite heavily in 2000, has been completely butchered and made unnessarily complicated in SSIS. The damn thing doesn't even remember your field mappings! And why do they make the VERY VERY common practice of automating importing multiple files from from a directory so hard? This should the first thing they implement, and it should be literally all point and click. bleh.
andrew h
Posted on: 8/7/2009 10:23 AM
# re: SSIS Tries So Hard To Make You Hate It
I completely agree, at first I was excited to try the new flashy MS tool, hoping it would improve on the unfinished DTS, but boy was I quickly disapointed! As mentioned many times, the simple csv import, which we used quite heavily in 2000, has been completely butchered and made unnessarily complicated in SSIS. The damn thing doesn't even remember your field mappings! And why do they make the VERY VERY common practice of automating importing multiple files from from a directory so hard? This should the first thing they implement, and it should be literally all point and click. bleh.
stefan b
Posted on: 9/1/2009 11:48 AM
# re: SSIS Tries So Hard To Make You Hate It
DTS was so easy a novice (like me 8 years ago) could figure it out in an hour. I've been struggling with SSIS for 2 years now and I still have to beat my brains out to get a simple table from one SQL database to another. It's killing me.
Bob
Posted on: 9/3/2009 2:16 PM
# re: SSIS Tries So Hard To Make You Hate It
SSIS is garbage, plain and simple. A complete pile of crap. A huge turd that I am pissed off about. I've been a database developer for 20 years, and have developed countless jobs to pump data. In DTS, I can import a simple text file in less than 3 minutes. In SSIS, I have spent an entire day trying to accomplish the same task. Microsoft has completely lost touch with the commonfolk, people who need tools to work very quickly for the most common tasks. SSIS is not only bug-ridden, but it is only usable by the .02% of Database Developers who have database servers in their living rooms and spend 23.85 hours per day programming. For the rest of us, SSIS is absolutely useless. Microsoft should be ashamed.
Charles Duwel
Posted on: 9/15/2009 12:38 PM
# re: SSIS Tries So Hard To Make You Hate It
I have to agree that SSIS for SQL Server 2005 is, for all intents and purposes, a beta product. A buggy beta product no less. I thought it was me - any slight change to a package can blow the whole thing up. I am working on a project to send data to our funders as xml and I was making a SSIS package to bring in the data to send. I gave up on SSIS and am using SQL Scripts to bring in the data until I have everything set and then I'll make the SSIS package.
The packages work OK when they are made but if a small change is made – watch out for falling errors. I added one field to a destination table and the same field to the view I was using as a source and – after shutting down and restarting VS – it still blew up. Even tables that weren’t changed at all and worked before had errors. That’s when I started looking around the web for others having the same type of problems and found I was not alone. Makes me feel a little better but what a hassle this is. DTS was much better.
Mookie Wilson
Posted on: 9/18/2009 3:46 PM
# re: SSIS Tries So Hard To Make You Hate It
SSIS blows. Especially when you have a 64 bit SQL Server, forget it, SSIS is worse than useless garbage, because, once you've struggled through the ubiquitous non-Unicode to Unicode error (hint: change your oledb source column types to nvarchar instead of varchar, if you can, to get rid of this error when exporting to excel, its easier than trying to get the moronic data conversion utility to work) everything works fine when you test and debug on your 32-bit PC but when you deploy it to the 64 bit server it s**ts the bed for 2 reasons, and probably more, I just haven't been able to surmount #2 so i can't see what future problems may lie ahead.

Reason 1.) Micros**t Corp. doesn't want to support the 64 bit JET-OLEDB driver, so you have to run it as an Operating System Command to execute the s**tty little thing in 32 bit mode, if perchance you need to, oh I don't know, connect to another little Micros**t family of products you may have heard of, they're called Excel and Access.

Reason 2.) So once you've gotten the cheat codes for #1 you now have to contend with this little gem. None of your packages will run because it can't decrypt some silly little vague thing in the XML representation of the package. If anyone has the cheat codes for this it would be highly appreciated if you would post any little arcane command line switches I need to get these silly little f**kers to work.

So now, not only do you not have a workable package, but you've been led on for days and days, like some clueless John being bilked into poverty by a bar trawling grifter, into thinking there was just a little glitch you weren't catching and all you have to do is find it and fix it and whoila your package will work. Uh uh, 1-1/2 years later and I still have not found any way to make this pos do what it was advertised to do.

In fact, I took the open source NExcel project in C# and built wrappers for it then compiled it as a CLR assembly and deployed it to the SQL Server in about 2 days in order to read Excel spreadsheets into SQL Server 2005. I also wrote my own assemblies to write out queries/tables to Excel files along with headers, to append to text files, overwrite text files, delete files, copy files, copy/delete/rename all files in a directory, etc. And I wrote it all from scratch in C# and it took me about 1/20th the time its taken me to learn how to create an unworkable package in SSIS. I'm done with SSIS, we should all start a SourceForge project so we can all jump in and cover for Micros**t's incompetence. I would never use a Micros**t product again, unfortunately I'm compelled to by my employer.
Mookie Wilson
Posted on: 9/18/2009 3:46 PM
# re: SSIS Tries So Hard To Make You Hate It
SSIS blows. Especially when you have a 64 bit SQL Server, forget it, SSIS is worse than useless garbage, because, once you've struggled through the ubiquitous non-Unicode to Unicode error (hint: change your oledb source column types to nvarchar instead of varchar, if you can, to get rid of this error when exporting to excel, its easier than trying to get the moronic data conversion utility to work) everything works fine when you test and debug on your 32-bit PC but when you deploy it to the 64 bit server it s**ts the bed for 2 reasons, and probably more, I just haven't been able to surmount #2 so i can't see what future problems may lie ahead.

Reason 1.) Micros**t Corp. doesn't want to support the 64 bit JET-OLEDB driver, so you have to run it as an Operating System Command to execute the s**tty little thing in 32 bit mode, if perchance you need to, oh I don't know, connect to another little Micros**t family of products you may have heard of, they're called Excel and Access.

Reason 2.) So once you've gotten the cheat codes for #1 you now have to contend with this little gem. None of your packages will run because it can't decrypt some silly little vague thing in the XML representation of the package. If anyone has the cheat codes for this it would be highly appreciated if you would post any little arcane command line switches I need to get these silly little f**kers to work.

So now, not only do you not have a workable package, but you've been led on for days and days, like some clueless John being bilked into poverty by a bar trawling grifter, into thinking there was just a little glitch you weren't catching and all you have to do is find it and fix it and whoila your package will work. Uh uh, 1-1/2 years later and I still have not found any way to make this pos do what it was advertised to do.

In fact, I took the open source NExcel project in C# and built wrappers for it then compiled it as a CLR assembly and deployed it to the SQL Server in about 2 days in order to read Excel spreadsheets into SQL Server 2005. I also wrote my own assemblies to write out queries/tables to Excel files along with headers, to append to text files, overwrite text files, delete files, copy files, copy/delete/rename all files in a directory, etc. And I wrote it all from scratch in C# and it took me about 1/20th the time its taken me to learn how to create an unworkable package in SSIS. I'm done with SSIS, we should all start a SourceForge project so we can all jump in and cover for Micros**t's incompetence. I would never use a Micros**t product again, unfortunately I'm compelled to by my employer.
Kirby L. Wallace
Posted on: 9/23/2009 10:01 AM
# re: SSIS Tries So Hard To Make You Hate It

Since SQL 2005, I get the distinct feeling that all the added components (specifically, SSIS and SSRS) are not MS products at all, but third party company products that the bought and ported (ie, crammed) into the SQL Server Suite.

SSIS looks nothing like DTS, nor even like SQL Server, and SSRS is something altogether different all over again, in a different sort of way.

Like most companies, MS seems to grow by acquisition. Like the Borg (STTNG), "You will be Assimilated. Resistance is Futile". Except this time all they did was say, "Sorry, we're outta brain implants and laser eyeballs... here... put on this sheet over your head and pretend you're one of us..."
SDW
Posted on: 10/30/2009 6:20 PM
# re: SSIS Tries So Hard To Make You Hate It
I got here by Googling "SSIS Horrible".

I frickin' hate that I must click "stop debugging" just to exit the damn thing after running a successful package. It just gets worse from there, if you dare edit anything.

What a bloated and butt headed tool, and a step backward from DTS! I certainly think DTS could have stood some improvement, but it least it was quick to set up, and portable...

SSIS stinks for porting SQL code that relies on #temp tables; they seem to disappear in between the little visual IDE tools. Arrghh. It's much simpler to write VBA, and rely on the rock solid ADO objects to transform data, or just do it all on the server in SQL afterward.

I'm about to inherit a pile of SSIS scripts from a departing employee. I'll try to maintain them, but am quite willing to just simplify and crudify things, if necessary. I don't have time to deal with all the obscure and untraceable errors that crop up in SSIS packages, or go digging into all the dialogs and property windows, when the main thing I'm after is to just move an Excel sheet somewhere.

Please Microsoft, get a clue!!
mariner
Posted on: 11/3/2009 4:16 AM
# re: SSIS Tries So Hard To Make You Hate It
That is what you get when bunch of Microsoft C compiler guys are assigned to implement an ETL tool! ;)
davetiye
Posted on: 11/3/2009 6:57 AM
# davetiyeci
güzel davetiye sözleri ve davetiye metinleri
Nathan
Posted on: 11/24/2009 5:43 PM
# re: SSIS Tries So Hard To Make You Hate It
Source controlling the jobs and deploying them to servers with different configurations has been a nightmare.
nick
Posted on: 7/1/2010 11:13 AM
# re: SSIS Tries So Hard To Make You Hate It
SSIS is garbage. No 64 bit excel jet drivers available. The packages fail without any good information. The execution results are about as useful as my grandma. This ssis is the worst product I have ever used to further my growth as a professional.
Adam
Posted on: 7/9/2010 12:52 PM
# re: SSIS Tries So Hard To Make You Hate It
SSIS sucks! It is horrible for all the reasons already stated here. I hate any time I have to deal with it. A single change to a package is never ever easy. Why is that? This tool is so cumbersome with any task. Even using source control on this is a major pain. Just looking through the code seems to apply changes to it. Can I not comprehend this tool whatsoever after having used it numerous times over the last 2 years? I guess I cannot. Whoever at MS created and let this thing out into the real development world are just awful at software engineering. What team there "dogfooded" this thing? Any? Did they only use it for THE SIMPLEST TASKS ever? There is no way they used it for anything that required a bit of complication. If they had they would have easily discovered SSIS sucks. Did they maintain the SSIS source code used in their "dogfooding" within a version control system? How did they account for changes and merges of the code? It is quite impossible I think to do a basic compare. Presentation layer mixed with the execution code? Who let that go through?

I can go on about how I hate SSIS but i have to waste my time right now using this awful piece of junk.
onilne roulette
Posted on: 7/30/2010 2:58 AM
# re: SSIS Tries So Hard To Make You Hate It
The most poorly conceived software upgrade Microsoft has ever release. I've asked staff to perform a few basic tasks that an untrained program could typically complete in less than 30 minutes using DTS. The same basic tasks e.g., exporting a table to a flat file and reordering columns take our new employees an average of more than 10 times the time to accomplish when they can complete the task at all. Microsofts in their never ending quest to bloat software packages with features has missed the mark with SSIS.
Is2gu
Posted on: 8/5/2010 10:26 AM
# re: SSIS Tries So Hard To Make You Hate It
What I can't figure out is why MS doesn't fully support dts on sql 2008 x64. SSIS is the worst ETL program on the market. But DTS is great, works well and is very solid. I have designed DTS packages for at least 10 years and SSIS may be able to do 10 times the stuff as DTS but I don't care if it takes 1000 times as long to import a simple file. For example I have a unicode import source that has over 300 columns that SSIS won't import without doing explicit conversions. In DTS creating this package would take about 3 minutes. In SSIS I spent at least a week building the package. MS really screwed this one up the fix is so simple....fully support DTS on x64 bit sql servers.
Chris
Posted on: 8/12/2010 2:30 AM
# re: SSIS Tries So Hard To Make You Hate It
Add me to the list of those who wasted most of a day trying to import a stupid text file into an existing table - something I could have built, from scratch, in about 15 minutes with DTS. After six hours, all I have is a bunch of unexutable packages that generate pages of largely indecipherable erros, and I only got this far because of Googling every 10 minutes.

I can't imagine using this tool for anything complex when it can't handle a basic flat file import.
Jacked In Jacksonville
Posted on: 8/20/2010 12:14 AM
# re: SSIS Tries So Hard To Make You Hate It

So, simple request. Schedule a job to export some query to an XLS sheet.

Okay. Fire up BIDS and prepare to lose a day or two. First, configure various tasks, then connectors, then get it to finally run locally after much tweaking.

Forget all the packaging stuff, which adds useless complexity, and just get the dtsx into MSDB on the server.

Try to run it there. Won't because of permissions, or 32/64 bitness, or drivers, or umpteen goddamn other mysterious reasons because the fucking errors don't tell you shit! Of course, try to gain some control by specifying the job parameters in SQL Agent, except SQL Server fucking changes them everytime you revist the job settings for SSIS tasks.

Read umpteen webpages for the slim hope of exiting this eight layer of hell.

Or, write a bcp one-liner and execute as a scheduled task. Bam. Done. Sure, it's not excel, but the PEBKAM users can convert it them-fucking-selves.