SSIS Catalog Validation Anonymous Logon Bug

For some reason when I changed the connection string parameters on my SSIS Catalog project I started getting a weird bug where the project fails connecting to the SAME server via a different with login failed error code. After some digging it looks like the project is trying to connect to the new IP via 'NT Authority\ANONYMOUS LOGIN' credentials instead of using the SQL Agent credential in the agent job. If I revert the connection string parameter back to the original IP (same server) there is no issues.

Well apparently this is a known bug:
https://connect.microsoft.com/SQLServer/feedback/details/727219/login-failure-message-during-ssis-2012-package-validation

And sadly it has not been fixed even with SQL 2012 SP1. Currently the easiest work around is scripting out the SSIS catalog job call and have EXECUTE AS LOGIN = 'domain\db service account' execute prior to the rest of the script.

Stupid issue but at least there is a work around. Hope this information can help someone else who is experiencing the same issues.

Datetime Parameter Mapping issue with SSIS

This might be common knowledge but I spent a good 30 minutes trying to figure out why my Execute SQL task wasn't inserting a datetime variable value in to my database. Generally when working with SQL I know that the datetime data type can convert to DBTimestamp with no issue but for some reason it keeps failing whenever I set the data type in the parameter mapping settings of the SQL task to DBTimestamp... Setting it to varchar or long would make the task succeed however it would have inserted a wrong date and time.

The fix? Simple.... set the data type to Date instead of DBTimestamp! DOH!

/cry

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.