SSIS on SQL Server 2008 R2

Hi all,

These days I had a chance to take a look at SQL Server Integration Service 2008 R2 (SSIS) and do you know what? it is a really good thing when you need to Extract, Transform and Load (ETL) data from a DataBase to another.

However, unfortunately it is very buggy and is still like something that is not fully implemented, like not all the functionality was implemented till the end.
What I've found during several days of investigations on SSIS is:
1) it is very hard to maintain;



2) sometimes when you have some sequence elements - you can not add more elements inside that sequence;

3) sometimes the designer goes crazy and you have nothing else to do that restart Visual Studio, and after restarting all the connections from your diagram will be lost;

4) you are not able to Undo/Redo your actions;

5) a lot of things are very hard to do, meaning the user interface is not very intuitive;

6) when you want to merge two sources then you first have to sort the input data, why this is not performed automatically?

7) when you have sensitive data then this data is encrypted using user key, or something like that, and then when you want to deploy the packages to a server, where you log using a different user it starts to throw all kind of exceptions, and it is really hard to understand what is wrong;

8) you can't arrange the packages from the solution in different folders, plus all the files from all the pre-defined folders in the solution are placed in the same folder and it creates a big mess of files in the solution file;

9) you can't create a deployment project;

10) you have to specify the path where the configuration file is, but what if on different servers I want different paths for different configuration files? in that case you will have to edit each package;

11) You have to change the connection string in each package if you didn't put them into an configuration file;

12) there are many other problems...

Some ideas that I have used to make the deployment of the SSIS packages easier are:
1)  I have used Environment variables to store the path of the packages on the server, plus the path to the configuration files.

2) I have saved the connection strings into configuration files;

3) I have created a batch file that creates a job in SQL Server, that job is running my MasterPackage;

4) I have created a MasterPackage that will be run, and will decide which child package when to run, this way I will only have one SQL Job for the MasterPackage and a custom configuration file for the child packages.

4) I have used some incremental loading techniques to do some ETL with my data.

In conclusion I can say about SQL Server Integration Services that it is a very powerful thing but, it is still very buggy and is not finished. I have read some articles about SSIS 2012, but unfortunately the Business Intelligence for that will only work on Visual Studio 11. I saw that they have added a lot of new functionality in it and also fixed a lot of bugs, lets hope that is true.