http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Wahey! On it goes... The latest gem I've found: If you output to a fixed width flat file with column headings that are wider than the column width, it bombs. e.g. columnname=Gender, datatype=STR(1) ha ha ha What is with this "it's free" argument. So is shit. "The customer is the person using the product and if they say it's crap then it is. You should listen to them." Well said young man! http://ayende.com/2637/ssis-15-faults#comment42http://ayende.com/2637/ssis-15-faults#comment42Fri, 07 Sep 2007 12:47:40 GMTTo Andy Leonard: The entire SSIS team should be put up against a wall and beaten with wet fish until they're grovelling on the floor, crying like babies. They need to feel our pain. SSIS is the software equivalent of a video recorder - if I have to go on a training course to learn something that should be intuitive, there is something seriously wrong with the product. Many years ago, when I was a (real) engineer, one of the things we had beaten into us was that if a customer says a product is crap, it doesn't matter what statistics or evidence or excuses you produce to show that it's great: The customer is the person using the product and if they say it's crap then it is. You should listen to them. (Actually, maybe I'm being too hard on the SSIS team. Maybe the managers are the ones who should be beaten with wet fish). The most disappointing thing about SSIS is that it isn't much of an improvement on DTS. DTS was straight-forward but had some irritating shortcomings that made it a bit of a pain (eg try changing the database name for a whole bunch of packages - same server, different database). Having used BusinessObjects Data Integrator, with its shared data sources, I learnt how it could be done right. Then along came SQL Server Reporting Services. Shared data sources, everything worked well, very intuitive. SSIS was going to be great! Now I'm actually using SSIS and it's crap. Seems like it has similar shortcomings to DTS (eg try using parameters in sub-queries) but with a less straight-forward interface. Gives the impression of just re-skinning DTS. Also, things, such as shared data sources, that work well in SSRS are rubbish in SSIS (eg change the server and database in a shared data source but find that my SSIS packages are still pointing at the old server??). Perhaps the worst thing is that stuff that just works in DTSs is broken in SSIS - I copied a large SQL statement from a DTS package into a SSIS package and got an error message saying SSIS couldn't extract the parameter from the SQL. DTS managed it ok, though. I've spent a couple of days now, trying to get a SSIS package working which is a direct copy of a DTS package on a SQL 2000 box. Given how simple the DTS package is (one variable that is set twice, a couple of data transform tasks with one parameter each) I can't believe how difficult the process is. http://ayende.com/2637/ssis-15-faults#comment41http://ayende.com/2637/ssis-15-faults#comment41Sun, 02 Sep 2007 22:28:12 GMTI was proficient with DTS and had no major problems with it, but I'm getting to the point now where I'd rather just use Python (our preferred language) to import data than use SSIS. I'm having particular problems with the XML Import component - my favourite was I had to change the XSD, prompting the SSIS editor to come up with the great Microsoft "it seems like your XSD is different, would you like me corrupt the entire package for you?" message. Yes, by clicking the fix I then got generic COM errors just trying to right click on ANY component in the package. Thanks for that. Trying to change the XSD manually didn't work either. I had to completely rewrite the package from scratch just to make that tiny change. Now my problem is using the Foreach enumerator to cycle through a bunch of XML files loading them using the XML Import. If the import fails (invalid XML etc) the file is then moved to the Reject folder and package continues to next file. Which it does if I run in debug mode in the Visual Studio editor. If I run it as part of a job or execute it from Integration Services it will work fine for the first 3 (sometimes 4) files that fail, but the 4th or 5th file will cause the whole package to fail (silently, as far as I can see there are no error messages indicating why). All seems fairly arbitrary. (it is the same 5 files each time I've tested it) http://ayende.com/2637/ssis-15-faults#comment40http://ayende.com/2637/ssis-15-faults#comment40Tue, 28 Aug 2007 14:37:06 GMTI was trying a simple package to take data from 10 servers and consolidate into one single server. I tried looping having one package and trying to change the connections, but I was not able to. So I thought I could do a copy of the packages 10 time, rename the packages and the config files, change the values in the config files and run them. 5 days past and every time I try to schedule the package, it is not taking the values from the config file, when I use dtexec or SQL Agent job ... It always wants to use its default config file that was in design time. I am without help :( http://ayende.com/2637/ssis-15-faults#comment39http://ayende.com/2637/ssis-15-faults#comment39Thu, 23 Aug 2007 21:48:47 GMTI understand your pains guys. In software nothing is set in stone. It takes time to develop quality product and especially refine it. btw I wanted to let you know we have a script task extension, which allows you to keep your SSIS script separate from your package. This will make it easier for you to maintain it and track changes. This is a partial help for point 7. We have other great ideas coming, soon. We will keep you posted. http://ayende.com/2637/ssis-15-faults#comment38http://ayende.com/2637/ssis-15-faults#comment38Sat, 18 Aug 2007 20:28:55 GMTDan, I have a 50MB file, which on row 30,423 has a comma instead of period in the float separator on the fifth column. Now, run that through SSIS, and then figure out what the problem is. http://ayende.com/2637/ssis-15-faults#comment37http://ayende.com/2637/ssis-15-faults#comment37Wed, 15 Aug 2007 19:18:30 GMTI agree with many of the points but I disagree about the bad errors. While the error handling is not perfect, it is way better than DTS. DTS provides cryptic and vague error messages when packages fail. SSIS provides informative (most of the time) and human legible feedback on package execution and errors. http://ayende.com/2637/ssis-15-faults#comment36http://ayende.com/2637/ssis-15-faults#comment36Wed, 15 Aug 2007 18:45:34 GMTNo message in the designer to tell you your cube is going to blow up from the limit on the 4 gig ASSTORE file limit even though it knows the record count and the width and data types of the column, it doesn't tell you this. Partitioning doesn't help with large dimensions. You can partition the fact tables till you are blue in the face and it won't help you if your dimensions are bigger then > 30 million rows. Cryptic Error Messages? Left see how about Error Messages 1: File system error: A FileStoreerror from WriteFile occurred. Means you dimension is too big. Hard to use designer? Talk about a massive learning curve, the dimension designer is horrible. The partitioning designer is even worse, why do I have to enter one query in at a time, and click six times to do the same thing 41 times. Rather then jerking around with the UI. Didn't they invent WPF to do compelling, easy to use interfaces, maybe someone should tell the guys on the SQL team about this. The flight recorder will fail and you get no error messages. The time out is set so that if you have a large table you have to know to hit the show advanced properties to be able to set the timeout. ExternalCommandTimeout and ServerTimeout to 0, by default it's set to 1 hour, this is a tool made to go against data warehouses, never occured to them it may take a lot of time. There is no way to configure the ASSTORE file limit, if you have a large dimension your stuck with ROLAP period. Even though everything else is configurable in msmdsrv.ini, go bloat your production data source with ROLAP tables. There is no way to configure it to use a different server to store the ROLAP tables, you have to use the source. http://ayende.com/2637/ssis-15-faults#comment35http://ayende.com/2637/ssis-15-faults#comment35Tue, 07 Aug 2007 21:34:22 GMTI agree with many of the original post's complaints, but find myself more willing to put up with them. In all, I find the platform acceptable whereas I was often unwilling to do things in DTS. My number one complaint, which I did not see mentioned, is that when I want to view details of a task on a package under source control, I cannot do so without it making some change (version?) to the file and wanting to check the file out. I'd call that one a bug. http://ayende.com/2637/ssis-15-faults#comment34http://ayende.com/2637/ssis-15-faults#comment34Mon, 06 Aug 2007 18:13:32 GMTI can see that everyone is having problems with SSIS but me, and I am using it for intense data transformations and transfers in an enterprise level environment. I see all the complaints in this post, and wonder how it is these problems occur. http://ayende.com/2637/ssis-15-faults#comment33http://ayende.com/2637/ssis-15-faults#comment33Sun, 05 Aug 2007 01:19:01 GMTRegarding point 14: you should use my DTLoggedExec tool: http://www.codeplex.com/DTLoggedExec it has been developed to solve this kind of problems. http://ayende.com/2637/ssis-15-faults#comment32http://ayende.com/2637/ssis-15-faults#comment32Tue, 31 Jul 2007 08:17:26 GMTAll, A response from MSFT here in case you are interested: http://blogs.conchango.com/jamiethomson/archive/2007/07/30/SSIS_3A00_-A-response-from-Microsoft-to-the-growing-criticism.aspx -Jamie http://ayende.com/2637/ssis-15-faults#comment31http://ayende.com/2637/ssis-15-faults#comment31Mon, 30 Jul 2007 22:14:06 GMTI too have struggled to implement and deploy SSIS packages. The learning curve is incredibly steep and I find myself unwilling to drag any other developer on my team into SSIS Hell! There are numerous bugs and inefficiencies with this tool that should have been addressed well before any official release. Our organization was forced to use it in order to create large data extracts for our clients through our proprietory web application. This was due to the fact that Reporting Services is also extremely inefficient when trying to render large reports. I won't even speak about Reporting Services PDF rendering. I was told a year and a half ago, after months of back and forth with Microsoft, that Reporting Services was not a mature product. Well neither is SSIS. In fact I'd say it's a preemie! And talk about a lack of TFS integration for SSIS! I can't tell you how many times I have had to re-add an SSIS Package that was mysteriously lost from the source control project. I would not assign SSIS development to my worst enemy. http://ayende.com/2637/ssis-15-faults#comment30http://ayende.com/2637/ssis-15-faults#comment30Mon, 30 Jul 2007 20:21:07 GMT75% of the points raised above are training and experience issues. :{> Andy http://ayende.com/2637/ssis-15-faults#comment29http://ayende.com/2637/ssis-15-faults#comment29Mon, 30 Jul 2007 19:01:38 GMT75% - 80% of _what_? http://ayende.com/2637/ssis-15-faults#comment28http://ayende.com/2637/ssis-15-faults#comment28Mon, 30 Jul 2007 18:56:36 GMTOren, Yep - really. A full 75% - maybe 80%. :{> Andy http://ayende.com/2637/ssis-15-faults#comment27http://ayende.com/2637/ssis-15-faults#comment27Mon, 30 Jul 2007 18:52:20 GMTAndy, really? Briefly, describe how you handle source control? branching & merging? How do you train VS to be faster? Also, considering the amount of people that agreed with me, including many that are disputing some of my points, I find it hard to believe. http://ayende.com/2637/ssis-15-faults#comment26http://ayende.com/2637/ssis-15-faults#comment26Mon, 30 Jul 2007 17:09:02 GMTTraining. http://www.endtoendtraining.com http://learning.solidq.com/na/CourseDetail.aspx?IdCourse=289 http://learning.solidq.com/na/CourseDetail.aspx?IdCourse=182 I lead the Solid Quality courses. Every single item listed in the original post and subsequent comments is addressed in Solid Quality's courses. I imagine they are addressed in the End-to-End training course and any MOC course you find as well. :{> Andy http://ayende.com/2637/ssis-15-faults#comment25http://ayende.com/2637/ssis-15-faults#comment25Mon, 30 Jul 2007 16:02:31 GMTDear all, Sorry, I forget to put these points. Storing your configuration in a table in sql with two fields like connection string and connection password, which the configuration wizard allows, puts the password in astrics signs and when you try to modify(try to use the configuration table to change the connection), and your password gets changed with the database server, and now if you put the passord ihere in the table, it shows as normal chars and the package simply dosen't run. The other major porblem is when your package is secured with password and you put the same package to another machine and try opening it, it somehow sometimes dosen't ask for password, load the package and showing errors in xml file for connection manager (as it is secured and I have not put the password) and they you have to go and again put the password and then close the solution and open and reset the connection string and then you are done. No proper management for the same has been given. So the points are: - bad configuration management - bad things in reading the same package in other machine Thanks, Jwalant Natvarlal Soneji http://ayende.com/2637/ssis-15-faults#comment24http://ayende.com/2637/ssis-15-faults#comment24Mon, 30 Jul 2007 06:08:21 GMTHi everyone, I fully agree with this article and believe me I myself, working on only 5-6 packages felt most of the issues. The worst case with me was to change the connection string databse name from dev database to prod database, when i finished doing the package and the package was approved by my supervisors, but they asked me to change the name of the connection from demo to prod and i wasn't able to change it easily, had to go with everything and that's true, there's no undo feature if u make a small change like this. Also, it goes lot more tedius, when you simply add a column to the table and feel that the same has been automatically selected in the datasource, without askiing for any confirmation and also the package starts giving error, While running the package from BIDS, it takes 40-50 mins and somehow when sceduled it ran for around 16 hrs. Oh my god! what the hell happened to my nicely designed and well tested package. Eventhat I was not able to figure that out. Not a good formater for things you have put in designed mode and if u selec autolayout all the things convert into hell and theres no good undo, also many times with auto layout, the sequence arrow overlap the components and It's no good. Also, with a simple package without any script task or .net programming its not possible to ask user, running the package mannuly, to select the source excel sheet, also if in the excel sheet named same as provided in the file connection (or in excel connection), its not possible to get data from the file if the sheet there is named something else otherthat what specified (like test instead of untitled) so, for me its - bad layout - bad table management - bad connection manager - bad excel shee connection. Thanks, http://ayende.com/2637/ssis-15-faults#comment23http://ayende.com/2637/ssis-15-faults#comment23Mon, 30 Jul 2007 05:48:34 GMTMen, the SSIS is free.... http://ayende.com/2637/ssis-15-faults#comment22http://ayende.com/2637/ssis-15-faults#comment22Sun, 29 Jul 2007 23:48:28 GMTAndy, No valid source control scenario isn't a HUGE lack for you? No, binary-like SCM is NOT a good idea. Slow as hell interface isn't an issue? All the repetitive tasks that it is expected you to do isn't an issue? So far, there has been HALF an issue that was resolved by me not knowing something, sorry I don't agree with this being an ignorance based posting. And as I am seeing an overwhelming agreement by many people about the faults that I have found, I am finding it hard to believe that it is ignorance that it the root cause of these issues http://ayende.com/2637/ssis-15-faults#comment21http://ayende.com/2637/ssis-15-faults#comment21Sun, 29 Jul 2007 18:21:35 GMT SSIS definitely has a steep learning curve - there's no question about it. I'm sorry to hear your experience with the platform has been frustrating and non-productive. I'm currently trying to learn C#. As you might imagine, I too sometimes ask "Why'd they do it that way?" Especially since I am learning from a machine code / Basic / Visual Basic perspective. I started coding in Motorola machine code just before my 12th birthday in 1975. I learned BASIC after that, so I guess that's a difficult habit to break (thinking like machine code and BASIC). I've built a half-dozen projects so far in C# - one will be published later this year in a Wrox book entitled Professional Software Testing with Visual Studio 2005 Team System: Tools for Software Developers and Test Engineers. The code is relatively simple and I needed help to develop some basic portions of it, but I struggled through it and, scrolling through my blog entries to verify, to date I've not once complained about the things I did not understand. For me, there is a distinct difference between "This sucks" and "I don't understand". Of the specific points listed above, I concur with #7. I deal with the pain of Oracle and DB2 data type mapping enough to see part of those points as well. I'm working on blog posts to (hopefully) address at least some of the data type mapping issues between SSIS and these environments. I'm doing this mainly for me - I don't enjoy having to figure this out every time I build a package against these sources. Putting this information in a blog post will hopefully help me as well as others. SSIS has shortcomings, but I do not see them as any greater or harder or more difficult than the limitations of other ETL environments. When you consider SSIS is a version 1.0 product (not a single line of code was migrated from DTS) and the platform's innovations, the strengths of the platform outweigh the hurdles and steepness of the learning curve. 75% of your points above are addressed with training and experience. I know - I've trained hundreds to use SSIS. My advice to you is: invest the time required to successfully develop using the platform. Good luck - and please let me know if there's anything I can do to help you overcome future SSIS hurdles. :{> Andy http://ayende.com/2637/ssis-15-faults#comment20http://ayende.com/2637/ssis-15-faults#comment20Sun, 29 Jul 2007 16:55:20 GMTI here you all with your errors and have fallen victim to many myself. However, I have also noticed that scripts running out on SSIS run faster then from SQL. Becuase time is money I keep coming back to SSIS but use the following work around of creating SQL scripts in SQL and then perform the SQL task in SSIS. This compromise works quites reliably and gets me benefits from both worlds. http://ayende.com/2637/ssis-15-faults#comment19http://ayende.com/2637/ssis-15-faults#comment19Sun, 29 Jul 2007 14:16:26 GMTManish, About extensibility, the issue is not if it can be extended, the issue is how hard does it make me work for it. From my company experience, it makes we work ridiculously hard to do it. About date formatting, what happens if my source is a flat file? About the XML, do you seriously suggest such a thing? The XML is neither human readable nor meant for human consumption. http://ayende.com/2637/ssis-15-faults#comment18http://ayende.com/2637/ssis-15-faults#comment18Sun, 29 Jul 2007 04:57:43 GMTI think some of the points are relevant but ofcourse not all the 15 points. 1) Points regarding errors are correct. 2) Complaint regarding its extensibility is absoultely wrong.If you are a .net developer , u will feel it the same as is being done in an application dev. project. All u need to do is inerit a class and just override its method. 3) Point 15 date formatiing can always be done via query.. Why do u want to do it in SSIS?? 4)Point 13, If u r aware of xml format. open ur package in XML and add as many fields as u want insetad of going through UI. Like above many other points are also seems to be irrelevant to me.. as one have his own thoughts.... I think we need to find always better ways to do things with the given things.Consider it like a IDE not as a ETL tool. It's much more than ETL , u can do hundreds of things with SSIS..and it rocks.. Manish. http://ayende.com/2637/ssis-15-faults#comment17http://ayende.com/2637/ssis-15-faults#comment17Sun, 29 Jul 2007 03:27:54 GMTAll, There's some great points on here but there is also some stuff that is completely unwarranted. I've made some comments here: http://blogs.conchango.com/jamiethomson/archive/2007/07/27/SSIS_3A00_-The-backlash-continues.aspx if you would care to take a read. Regards Jamie http://ayende.com/2637/ssis-15-faults#comment16http://ayende.com/2637/ssis-15-faults#comment16Fri, 27 Jul 2007 06:36:01 GMTAdolf said: "Setting 'sort order' on a column, does not actually sort the data" its not supposed to. Try reading the documentation. http://ayende.com/2637/ssis-15-faults#comment15http://ayende.com/2637/ssis-15-faults#comment15Fri, 27 Jul 2007 05:13:29 GMTOne from my blog: http://joelmansford.wordpress.com/2006/06/15/sql-server-2005-ssis-cant-export-xml/ If you've generated some XML from a T-SQL statement such as SELECT Col1,Col2,Col3 FROM myTable WITH XML AUTO You can't export to a file (i want xyz.xml) as the XML column is of type DT_IMAGE which isn't supported by the flat file destination http://ayende.com/2637/ssis-15-faults#comment14http://ayende.com/2637/ssis-15-faults#comment14Wed, 25 Jul 2007 16:22:20 GMT> Not possible to debug script tasks It actually is possible, just not consistently. http://ayende.com/2637/ssis-15-faults#comment13http://ayende.com/2637/ssis-15-faults#comment13Wed, 25 Jul 2007 12:36:39 GMT