Tuesday, October 9, 2012

Why SQL 2012 SSIS Catalogs are useless

When I first got started playing with the new SQL 2012 SSIS Catalogs I was pretty excited; To be able to use project parameters instead of config files and using the new project deployment model instead of the package deployment model makes life alot easier in the world of managing and deploying SSIS packages.

However there has been a few issues with the new features that has been a pain to work with:

  1. Project hangs indefinitely due to the new SSISDB logging
    Packages that use to run in SQL 2008R2 now hangs with SQL 2012. This may be caused by deadlocks with the SSISDB database. Turning off logging in the job resolves this issue however when I need to troubleshoot anything I have to manually turn it back on and off again.
  2. SSIS Server Maintenance Job breaks jobs and causes locks
    This is a default job created when installing SSIS 2012. It cleans out all old history and project revisions as configured. However when this job is running it will cause all other jobs to fail as it is placing a lot of locks on the SSISDB table. The job itself can take 3-4 hours to complete which means our other jobs are failing in the meantime causing massive backlogs.
  3. SSISDB logging and reports are tediously slow to use
    It's nice to see that Microsoft decided to create better reporting features with SQL 2012 however it is way too slow to use and navigating it to find issue is painful. Not only do I have to deal with the slow response time, but navigating between pages and finding issues is hard to figure out.
In summary we have decided not to use SQL 2012's SSIS catalog till SP1 hits, hopefully all the issues I listed above will be resolved then.

2 comments:

  1. We just encountered issue 1. Do you know if Microsoft is working on a solution?

    ReplyDelete
  2. I remember I saw a bug report but I don't know if they have fixed it yet. We have applied SP1 and have been patched up to date but the issue is still there. Hopefully they will fix it soon.

    ReplyDelete