Ayende @ Rahien

It's a girl

SSIS' 15 Faults

I dislike SSIS intently, and I say this as someone who has done two projects using it, and has spent much time recently struggling to work with it .Instead of harping how much I dislike, I decided that it would be better to list the things that I find it so hateful:

  1. Bad Errors: The number one reason that I am using .Net instead of C++ is not the garbage collection, it is the errors. Clear, understandable errors, with the ability to trace them back into their source. Errors that gives you the reason for what happened. SSIS' errors are anything but useful. Often you will get some sort of a cryptic COM error, and maybe you will get lucky and get the message from the database, instead of relying on SSIS's errors.
    The errors are not only hard to figure out, they are often hard to find, hiding in a mess of all the other information (mostly useless) that SSIS throws at you. No attempt is made to make it easier to locate and find the errors, those are left as an exercise for the user.
    The last time I felt this alone I was working with Pascal, trying to understand how the code moved the spindle of the HD using ASM commands.
  2. Random Errors: I like predictability. One of the worse things that can happen to you is that you get an "sometimes it happens, but I don't know why" error report. With SSIS, I literally had the entire project breaking up because I made such a significant change as changing the connection string. They are few things that I like less than having to deal with stuff that make me work in a stupid way, SSIS' way is to force me to go through all the boxes in my package and approved that I want it to do the same thing that it did before.
  3. Keeping track of what it shouldn't: I wish I had a dollar for every time that SSIS kept track of something it shouldn't. Be it the old configuration, hard coding the connection string inside the package and completely ignoring the configuration... etc.
  4. Sorry excuse for deployment: As you can probably see from above, SSIS's previous faults doesn't make for a nice transfer to production. I had to deal with that today, and it was a pain. Things just refuse to work if you move them between machines, when the database target remains the same. In most cases, those are "need new metadata" or plain "I am broke" errors, which leave you with the tried and true method of getting VS, opening the package, and starting to mess around with that manually, until it decides that it doesn't hate you so much again. Requiring VS to deploy successfully is a big mistake, but I have been unable to avoid this requirement in any package of any complexity so far.
    Would I need to do it again when I want to redeploy? Why, yes! It is not like I have better things to do...!
  5. Security? Who needs that: I should also mention that SSIS packages requires sysadmin rights to run when scheduled as a job. Which of course it will not tell you until you have run the job. I am aware of the agent proxy solution, it still remains a mystery to me why this is a requirement, after all, you can run an SSIS package just fine from inside SQL Server, just not as a job.
  6. UI formatting instructions along side with the executable code: Here is another huge mistake. The SSIS package contains both the executable blocks and the formatting for it, making it completely unreadable from plain text perspective, and making it impossible to understand what has change from a diff of the file.
  7. No thought about version control: As long as we touch that, SSIS packages and configuration might as well be binary objects, they are completely opaque for version control, and the decision to make the configuration files for SSIS un-indented was made by someone who really never had to modify a configuration file outside of the pretty UI, or wanted to actually see what is going to change.
  8. Bad configuration scheme: This bring me to the configuration scheme itself, yes, you can put the configuration anywhere you want, from incomprehensible XML files with hard coded paths to SQL tables to which you will have to have an hard coded connection string, from environment variables (WTF?!) to stored inside the package.
  9. Random configuration scheme: The problem with all those options is that SSIS seems to choose between those at random. At times it would choose the connection that it had assimilated inside the package, at times it would go to the wrong file, get the old configuration, or just complains that it is not having fun and that I should baby sit it again.
  10. Bad UI: If we are talking about baby sitting, that is what the UI makes me feels like. Having to feed it very carefully, in smaaaalll bites, what I would like it to do. Going through six dialogs and three property grid just to get something to work is not my idea of fun, and SSIS has the lovely errors to point your way. Oh, and there is the advanced property dialog, if I was feeling stupid.
    Then there are the worse issue, building expressions is horribly broken from the UI perspective, you can see the evaluated string if it is bigger than a few lines, and no one will help you if you dare break the source string into multiply lines.
  11. Lack of extensibility: My company has actually developed a series of components for SSIS, they cost a lot of time and frustration, so I can honestly say that trying to extend SSIS is nothing but pain.
  12. Bad interoperability: As it happened, I am dealing with Oracle. As an integration services platform, I fully expected SSIS to support this little known database, but it doesn't. I will spare you the pain of "CREATE VIEW Customers_SSIS_DOESNT_LIKE_ORA_TABLES as SELECT * FROM Customers;", but I assume your can guess the rest.
  13. Busy work: I mentioned it before, but SSIS is very click happy, requiring your to do a lot of work with the mouse, over and over again. Just trying adding a field to a data source, see how much pain you have to go through as you have to go through each and every one of the steps that it passed along the way, even if they never touched that field. Heaven forbid that you dare to remove a field. Did I mentioned the lovely huge dialog when SSIS basically tells you: "Nothing have changes, shall I map with the same column names?"
  14. Hard to debug: Today I had a dynamically generated SQL inside an SSIS package that was causing an error. I had no way of finding out how to get this dynamic SQL (which was stored in a variable), eventually I created a secondary path that saved the variable to  a table, where I could read it.
    Update: This is possible, see here for the details. The rest of this point still stand, though.
    Finding out why things are failing is a task for those who have little respect for their time, for SSIS insist that it is important enough to engulf all your time. Oh, and have fun getting a MESSAGE BOX from the SSIS process if a script task has thrown an exception! Obviously this message box will be hidden behind SSIS, so you will wait for the process to end for quite some time before realizing what have happened.
  15. The missing basics: I lost count of the many things that I consider a given that SSIS doesn't have. From date formatting to parameterized queries (to Oracle) to the basic UPSERT support. There have been a few times when I literally count not believe that it didn't have this capability. Date formatting and parsing is a basic part of what you ought to get out of the box with an integration package, as just simple example of a basic lack that is driving me crazy.

There are more, but I am going to walk the dog now.

Comments

jdn
07/15/2007 10:11 PM by
jdn

I feel your pain. 1 and 2 by themselves make it hard to do anything with SSIS besides basic data loads, maintenance plans, etc.

One other thing you might include:

No throttle control. It does allow you to set some thread settings, and write out files to a temp path as it does what it wants, but I had cases where it would absolutely swamp a well-built dev machine so much that the machine became absolutely unusable as it ran.

And then 35% of the time, it would fail. Silently. Not even bad errors. Just stop.

I want to like SSIS. I really do. But I can't.

Adam Tybor
07/16/2007 01:26 AM by
Adam Tybor

Couldn't agree more. I had the opportunity to lead a data warehousing project when it was beta and so I feel your pain. Perl actually became a good friend of mine and I used to generate templates for our packages. That worked fine for creation but maintaining 200+ packages all doing close to the same thing just with different meta-data is pure torture. Thanks MS

Have a look a sawzall, now that is a tool for data processing!

http://labs.google.com/papers/sawzall-sciprog.pdf

Jon Limjap
07/16/2007 02:10 AM by
Jon Limjap

Well, as with many Microsoft development technologies, SSIS wasn't really made for developers. It was made for DBAs who knew "a little about" development.

But it was touted as a full-blown development product.

Shaun Cartwright
07/16/2007 07:59 AM by
Shaun Cartwright

Been there with DTS on SQL2K. I spent many days working out (using the advanced settings - yeah right!) how to make a working DTS package that was distributable. Oh and the agent proxy thing does work well, but why do you have to change it for the whole SQL server process. If your integrating with an already existing database, the DBAs aren't going to be happy. I think I'll steer clear of SSIS for as long as I can... BTW hints from www.sqlis.com and with DTS www.sqldts.com

Steven
07/16/2007 11:53 AM by
Steven

6 & 7 are reason enough to not use ssis. Last week, I needed to merge a branch back into the main branch (SVN): with SSIS this is just impossible! Luckily I had written down the that changes that I made (well knowing that merging ssis packages would be impossible) so I could do them again in the main branch. But this is exactly the kind of silly and dangerous work I try to avoid by using a source control tool! Now if only SSIS would support this way of working! To show how bad it is: you should try diff'ing a ssis package where you only changed the value of a variable.

PS. The rest of your points also sound very familiar: I even wrote a custom configuration task that reads its settings from an simple ini file... I've been thinking about a very similar entry on my blog (10 things I hate about ssis), but haven't found the time to finish it.

Adam Machanic
07/17/2007 02:18 PM by
Adam Machanic

I agree with you 100%. Especially regarding version control. I asked for the ability to split up packages into multiple files to avoid problems with moving a control two pixels to the left and getting a whole new version... and was told that, sorry, they just can't handle it for another five years :(

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253500

That said, it's quite a bit better than DTS in many ways...

Adam Machanic
07/17/2007 02:25 PM by
Adam Machanic

Ooh, and I want to rant a bit more! Create a package that inserts some data into a table with a VARCHAR(15) column. Then go into the database and change the column to VARCHAR(20) and YOUR PACKAGE WILL BREAK!!! Arrghh...

And if you want a really fun day, try changing a connection manager from SQL Native to some other provider. Oops, you can't--it's greyed out. So try deleting the connection manager, then re-creating one with exactly the same name but a different provider, and... You'll discover that everything internally is referenced by a GUID, and your entire package is broken.

And you will no doubt discover this after you've been working with the package for over six hours, and since there is no undo...

adolf garlic
07/23/2007 12:56 PM by
adolf garlic

only 15?

copy/cut/paste causes random serialization errors

can't copy connection managers with their tasks

connection "managers" cannot cope with simulataneous multiple data flows

vs.net just blows up for no reason

sluggish due to constantly trying to validate everything all the time

piss poor excel integration

no upsert task/component

stupid proprietry datatypes

terrible navigation experience

crappy expression language "similar to c++" and editor

overly verbose but unhelpful error handling

hyperlink error references to the ms website which mysteriously do not exist

you end up working with a "what the hell buttons/checkboxes things do i have to delete, remove, add in order to get this frigging dialog box to feck orf?" mentality

unable to properly preview excel "too many fields"

"suggest types" correctly identifies dates, then claims that they are not dates/wrong format error

have to click on about 5 things to physically view the script in a script task (hello microsoft, the clue's in the name!)

crappy column mapping drawy liney thingy which is tedious in the extreme

load packages is slllllooooooooooooowwwww - makes windows 98 look good

"turd on a stick" sql editor

etc

Joel Mansford
07/24/2007 02:20 PM by
Joel Mansford

I'm with you guys on this.

I once needed to FOR-Loop through some SQL tables picking out data for individual countries. I would then put these in to a temporary table, export as CSV and ZIP them all up. I would then FTP each to a different FTP server.

All was going well until I discovered that the FTP Server connection manager has 'security' on it. This means it's not possible to programmatically change the password....

MS's workaround - use a SQL table as the configuration store and then issue an UPDATE to the appropriate row on each iteration through. I can't help thinking they were missing the point.

There really should be a configurable store option for NO SECURITY, the protect sensitive and whatever else are fine but really don't lend themselves to working in a dev team where you need to share these files. It's not like developers aren't used to having to store connection strings etc. in their code!?

My biggest gripe though is how SLOOWWWW it is. If you have a solution with say eight complicated scripts in it it can take 5mins to load on my dual-core Pentium-D 930 with 3Gb RAM

Joel Mansford
07/24/2007 02:22 PM by
Joel Mansford

Oh, and I forgot to mention as with Oracle it's impossible to get data in to or out of MySQL using SSIS, even with every .NET data provider under the sun.

Hurray for interoperability!

Joel Mansford
07/24/2007 06:59 PM by
Joel Mansford

Man, the gripes keep coming...

One which really got us was that there is no support for transforming / moving SQL_VARIANT columns. This is despite support for them in .NET 2.0.

We got around it in quite a hacky way by casting them to a varbinary (or varchar can't remember now) and then recording in a separate column the original type so that we could cast back on the other side.

I logged it on the MSDN Connect / Feedback back in March 2006

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124886

Lets see if SS2008 has it sorted...

adolf garlic
07/25/2007 12:31 PM by
adolf garlic

and another thing...

Setting 'sort order' on a column, does not actually sort the data

Not possible to debug script tasks

Ayende Rahien
07/25/2007 12:36 PM by
Ayende Rahien

Not possible to debug script tasks

It actually is possible, just not consistently.

Joel Mansford
07/25/2007 04:22 PM by
Joel Mansford

One 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

Jamie
07/27/2007 05:13 AM by
Jamie

Adolf said:

"Setting 'sort order' on a column, does not actually sort the data"

its not supposed to. Try reading the documentation.

Jamie Thomson
07/27/2007 06:36 AM by
Jamie Thomson

All,

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/SSIS3A00-The-backlash-continues.aspx if you would care to take a read.

Regards

Jamie

Manish  Kumar
07/29/2007 03:27 AM by
Manish Kumar

I 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.

Ayende Rahien
07/29/2007 04:57 AM by
Ayende Rahien

Manish,

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.

Pieter
07/29/2007 02:16 PM by
Pieter

I 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.

Andy Leonard
07/29/2007 04:55 PM by
Andy Leonard

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

Ayende Rahien
07/29/2007 06:21 PM by
Ayende Rahien

Andy,

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

Martin
07/29/2007 11:48 PM by
Martin

Men, the SSIS is free....

Jwalant Natvarlal Soneji
07/30/2007 05:48 AM by
Jwalant Natvarlal Soneji

Hi 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,

Jwalant Natvarlal Soneji
07/30/2007 06:08 AM by
Jwalant Natvarlal Soneji

Dear 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

Andy Leonard
07/30/2007 04:02 PM by
Andy Leonard

Training.

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

Ayende Rahien
07/30/2007 05:09 PM by
Ayende Rahien

Andy, 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.

Andy Leonard
07/30/2007 06:52 PM by
Andy Leonard

Oren,

Yep - really. A full 75% - maybe 80%.

:{> Andy

Andy Leonard
07/30/2007 07:01 PM by
Andy Leonard

75% of the points raised above are training and experience issues.

:{> Andy

James Novotny
07/30/2007 08:21 PM by
James Novotny

I 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.

Jamie Thomson
07/30/2007 10:14 PM by
Jamie Thomson

All,

A response from MSFT here in case you are interested: http://blogs.conchango.com/jamiethomson/archive/2007/07/30/SSIS3A00-A-response-from-Microsoft-to-the-growing-criticism.aspx

-Jamie

Davide Mauri
07/31/2007 08:17 AM by
Davide Mauri

Regarding point 14: you should use my DTLoggedExec tool:

http://www.codeplex.com/DTLoggedExec

it has been developed to solve this kind of problems.

Rick MCSD
08/05/2007 01:19 AM by
Rick MCSD

I 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.

Brian
08/06/2007 06:13 PM by
Brian

I 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.

SQL_DBA
08/07/2007 09:34 PM by
SQL_DBA

No 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.

Dan Crowell
08/15/2007 06:45 PM by
Dan Crowell

I 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.

Ayende Rahien
08/15/2007 07:18 PM by
Ayende Rahien

Dan,

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.

Ivan Peev
08/18/2007 08:28 PM by
Ivan Peev

I 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.

Saran
08/23/2007 09:48 PM by
Saran

I 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 :(

Mark
08/28/2007 02:37 PM by
Mark

I 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)

SimonTeW
09/02/2007 10:28 PM by
SimonTeW

To 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.

adolf garlic
09/07/2007 12:47 PM by
adolf garlic

Wahey! 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!

Comments have been closed on this topic.