So a few weeks ago I made a switch from our SSIS 2008 package to the new SSIS 2012 project deployment model. I really love having the new project parameters feature (instead of config files) and I enjoy the ability to encase all the sub packages as one big project... My biggest issue with the new SSIS 2012 Catalog is SSISDB and it's reporting feature. Since we have packages that runs every minute and uses multiple threads, the SSISDB transaction log fills up FAST. We resolved this issue by changing the database mode to SIMPLE however performance still becomes an issue with this many transactions. We ended up turning off logging for our packages (until something breaks) and this seems to resolve a lot of issues. So here are my questions thus far: Is there a way to make a central SSIS server that host all the projects and have child servers process the ETLs using the child's resources?How do you all manage the SSISDB database? Size? Performance?This bugs me 'To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report'.. why can't we at least see the last onError message in the Log File Viewer like before? That made stuff so much more simple!What the heck is up with the SSIS Server Maintenance Job? It seem to screw everything up for a few hours! In the end I reverted our major project back to the package deployment model instead of the project deployment model. Any inputs? Thanks!

Hello Chris, I have experienced issues with reporting out of SSIDB. Not with the T log as such, more to do with the volume of event_message data and the time it takes to query it. Responses to some of your questions: "Is there a way to make a central SSIS server that host all the projects and have child servers process the ETLs using the child's resources?" - No, but I have requested something very similar feature here at https://connect.microsoft.com/SQLServer/feedback/details/726102/ssis-catalog-agent#details A vote and a comment would be appreciated. "How do you all manage the SSISDB database? Size? Performance?" - On dev I just purge the contents periodically. As yet I'm not running big volumes into SSIDB on PROD so I don't have an issue - I'll cross that bridge when I come to it. "This bugs me 'To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report'.. why can't we at least see the last onError message in the Log File Viewer like before? That made stuff so much more simple!" I find myself using the inbuilt reports less and less in favour of good old SSMS. <blatantPlug>If you're interested in a different approach then I have some open source reports that you leverage: http://ssisreportingpack.codeplex.com/ </blatantPlug> Hope that helps JTObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hey Jamie, Thanks for that information. I voted for the idea of a centralized SSIS Catalog store that you linked. I completely agree that it's something that we all need. For your SSISDB, what level of logging is running on your packages? Thanks for the reporting pack.. I will check that out ASAP. What are your thoughts for the SSIS Maintenance Job? Does it screw up any ETLs that you need running 24/7? Many thanks once again. Chris

Hey Jamie, Thanks for that information. I voted for the idea of a centralized SSIS Catalog store that you linked. I completely agree that it's something that we all need. For your SSISDB, what level of logging is running on your packages? Thanks for the reporting pack.. I will check that out ASAP. What are your thoughts for the SSIS Maintenance Job? Does it screw up any ETLs that you need running 24/7? Many thanks once again. Chris I use LOGGING_LEVEL=Basic. Verbose has some useful stuff in it but also has far far too much superfluous stuff - so I choose not to use it. No thoughts on the SSIS Maintenance job I'm afraid - I don't use them.ObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Do you get any hanging issues with SSIS 2012 Catalog? When I run it in package mode it doesn't hang... only in project mode. :\

Hi Chris, You may be experiencing the deadlock scenario discussed here: FIX: You experience a deadlock condition when you run multiple SSIS packages in SQL Server 2012 http://support.microsoft.com/kb/2699720 Thanks, Cathy Miller

Hi Chris, You may be experiencing the deadlock scenario discussed here: FIX: You experience a deadlock condition when you run multiple SSIS packages in SQL Server 2012 http://support.microsoft.com/kb/2699720 Thanks, Cathy Miller I think this could be part of the issue... however even when I run the package one at a time it still hangs at one random package consistently... This is still GREAT to know because we did originally have multiple large project that could have been starting at the same time. Each package runs multiple parallel threads that imports files in to a database. What I noticed is that if I turn basic logging off it will actually go though the whole load with no issues. With logging on it will hang so maybe I'm getting a deadlock on another procedure?

Hi Chris, I haven't heard of that issue. Perhaps you can submit it to our Connect site - http://connect.microsoft.com. Or, you could create a support case for more in-depth troubleshooting: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone If Microsoft determines that a problem is the result of a defect in a Microsoft product, you will not be charged for that incident. About your question on the SSIS Maintenance Job, what exactly is happening? Thanks, Cathy

Hi Chris, Actually, here's a possibility: 2735763 FIX: Execution of a package ends unexpectedly and status is "Canceled" when you execute many SSIS 2012 packages in parallel - http://support.microsoft.com/kb/2735763/EN-US Do you have Cumulative Update 3 installed? Thanks, Cathy

Hi Chris, I have heard that CU3 does fix the problem caused by the SSIS Maintenance Job. Thanks, Cathy