Thursday, August 1, 2019

MSSQL: Datawarehouse: catalog.executable_statistics view missing

Trying to query the catalog.executable_statistics view and the object is missing. The reason is, there isn't SSIS project or packages being set up and deployed on this database. Quick viewing for SSISDB  (it can be any name aside from SSISDB) did not show up the Databases list. 


Msg 208, Level 16, State 1, Line 4
Invalid Object name 'catalog.executable_statistics'.




Integration Services Catalogs have no Projects or Packages.





Let's create Integration Services Catalog


Step 1: Create Catalog






















There is a bug on my Create Catalog screen where the OK button is missing. Display Resolution issue.























Step 2: After creating the Catalog, right-click and Create Folder.
























Step 3: Once Folder is created, Deploy the project.




















Note: I have created a project to perform ETL prior to this exercise. So my package already existed.

















The path can be located in the Visual Studio after the package is built.



























Note: I have a little glitch here where the Next button is grayed out. Go into the Server Name and reselect and it will repopulate the Server Name and now the Next button is activated and the Path has a path as well. 









Completed the Project Deployment and package is created.



Step 4: Now execute the package.




The SSISDB should be created by now and the catalog.exectable_statistics should work.



That's it. Executable_Statistics view is great to provide information for a package execution time, duration and results.



References:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017




MSSQL: Getting more detail information for failed jobs.

Often when a job failed, it provides trivial information for diagnostic. In order to expand the detail of the failed job, it can be done th...