Why I hate SSIS

In one of my last project we decided to use Microsoft SQL Service Integration Services. Things started well off and we instantly fell in love with it.
Our requirement was to create some kind of scheduled job (or service) that would run at regular intervals and load a number of XML files, transform that XML and save it to our data base.

We did a POC with SSIS and it seemed the perfect technology providing all the features that we were looking for to load, transform and save the data in XML files to DB.

However, our liking for SSIS was short lived when we started to build the actual package.

These are the issues we faced:

1. All external .net assemblies that SSIS packages refers to should be deployed to GAC.
2. If the referenced assembly's version number changes, it requires us to re-build all the script tasks in our SSIS package for them to work with the new assembly.
3. SSIS allows us to save configuration information to database and we used that feature. However, we cannot store connection manager strings with user id and password to DB. SSIS removes the password before storing the connection string to DB.
   Why we used connection string with user id and password instead of integrated security is another issue (and a very bad design on our part) which I would not like to discuss.

While were able to handle the other issues, the 2nd point was a pain  for us as we had automated builds that increment the assembly version with every build. We were not able to identify a workaround for this.

So, how did we handle this?
It's simple we dumped SSIS (:)) and relied on our old pal the windows service which provided a lot more flexibility and was much easier to schedule.

If you are planning to use SSIS in your project then beware of these issues.
My suggestion would be to use SSIS only if we do not plan to use any common library in it.