SSISDB Reporting: Now What?

Thanks again to everyone who attended my session today on SSIS DB Reporting: Now What? At the end of the presentation there were several questions that came up that we didn’t have time to answer. Below is the Q&A from the session, I hope this answers everything for everyone. Please feel free to contact me Twitter (@sqlbischmidt) or via the comments section if you have any additional questions. The code for the SSRS reports and the PowerPivot workbook is here.


  • I see a lot of system tables here. Over a period, these tables could accumulate enormous amount of log data. Is there a built in archive/purge job OR do we have to build our own?
    • It’s a customizable property. If you right click on the SSISDB Catalog, and select Properties
      • image
    • There is a retention period property, which is the number of days SSIS keeps a log of the history.
      • image
  • We are using SSIS 2008 in our company and we have adopted file based deployment for SSIS package as opposed to deployment to MSDB. With 2012, I see a lot of features with SSISDB. Do you think we will have a disadvantage to continue with File based deployment, when my company moves to SSIS 2012?
    • I do, simply because of everything the project deployment model in 2012 gives you. While I didn’t explore it too much for this session, I’m sure you can link the SSISDB and MSDB to get some really powerful reports. Eventually, I think project deployment models will become the norm, but right now it’s a period of transition for everyone. It’s still pretty new.
  • Can I deploy an SSIS project from TFS through power shell?
    • In Windows Server 2012 and SQL Server 2012, a lot of the components and classes have been opened up to PowerShell, I don’t believe there would be any reason you couldn’t.
  • Is the duration for each control flow item logged in the execution table?
    • Not within the execution table, but there is another one called executable_statistics that contains the information for each component within the control flow. The execution table only contains information about packages as a whole.
  • Since you mentioned Source Control System, is there any "Source COntrol" in 2012?
    • TFS, Subversion, and others have all been adding capabilities to handle 2012.
  • Do you need Admin permissions to query the SSISDB tables?
    • No, you don’t necessarily need to have admin access. Your admin could grant you the same access as you could get to any db.
  • Does BI-xPress SSIS Console Monitor use the reports from SSISDB Catalog or is it based on custom logging?
    • It’s based on something custom.
  • Do all SSIS packages have to run via SQL Agent Job?
    • No, you can call SSIS packages in your preferred choice. dtexec, through the IS catalog (2012 Project deployment model), through the integration services instance (2012 Package deployment model), or via tsql (either project or package deployment model in 2012)
  • Have they added support for the geography data type
    • Not that I’m aware of. SSIS still treats geospatial data as a DT_IMAGE(BLOB) when it’s moving across. For more information, go to this link. (
  • what if I am using a project deployment model but only want to deploy one of the packages in the project?
    • In the current release, this wouldn’t be possible. You would have to keep the project as either the package deployment model and deploy them one at a time or in the project deployment model and deploy the entire project together.
  • Can you still deploy using manifest file?
    • Yes, as long as you are using the package deployment model (see next question)
  • How has deployment of SSIS packages changed from 2008 to 2012?
    • In terms of the package deployment model, not very much. Everything that was done before is still (technically) supported. The biggest change is the project deployment model. This gives the ability to treat a collection of SSIS packages
  • can we disable datataps?
    • Yes, datataps aren’t a default. You’d have to specifically reference them. In fact, datataps do affect performance and buffering, so make sure you have an actual need to put them in your package. When calling via tsql in either job step or ad hoc, there is an add_data_tap stored procedure that you need to call to execute your package. No xp_cmdshell! 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s