Sunday, August 19, 2012

How can SSIS be improved

I am not pretending to be an SSIS guru, especially since I started using SSIS only in the last two months and I am currently finishing my first project. But during my relatively short experience I encountered several problems, that could not be solved in an easy, elegant way with Business Intelligence Developer Studio 2008: 1. I needed to implement a sequence of steps in a data-flow task and have this task execute in parallel on different tables. What I was trying to do was exploiting parallelism through domain-decomposition. that. I needed to parameterize the data source in the data-flow task somehow but I was not able to achieve that. I tried passing parameters from a parent package to a child package or using a stored procedure with input parameter table name, but none of this was easy enough, Finally, I gave up and created copies of the same sql code in several stored procedures and had each stored procedure execute in parallel on its own table. 2. Excel is a nightmare for me. I always get errors about missing 64 bit drivers , so I finally had to create a 32 bit separate project just for the pure purpose of importing from Excel. 3. Fuzzy lookup only works in 64 bit. When I finally decided to work on one 32 bit project for the sake of Excel import, I found out that I actually need another 64bit project for the fuzzy lookup. 4. The regular lookup does not update the table when I specify "Replace column" for the matched records. I need another step to update the IDs with the matched ones from the database. It will be really nice to have this as an option in the lookup component - it's so naturally needed. 5. I cannot easily organize the visual appearance of the steps in my project. I could not find options to align left edges or right edges of the tasks. I had to spend some time positioning elements so that my workspace is not messy. 6. I don't think the XML export is supported. I also want to export my data in Excel and format the column lengths in advance, but this is not a trivial task. Perhaps some of these tasks are actually achievable, but I just don't have the knowledge yet. I am open to any suggestions or comments and will appreciate them!