Here is a PowerShell script that I use to provision and setup SSIS Catalogs when I’m demoing SQL Server 2012. It does the following: Connects to localhost (default instance) Drops the existing SSIS Catalog Creates a new SSIS Catalog Creates a Folder Deploys a Project Creates an Environment Creates three server varaibles Creates an environment … Continued
Exit Codes, DTEXEC, and SSIS Catalog
DTEXEC has two execution modes when running packages in the SSIS Catalog. By default, the process runs asynchronously – the package execution is scheduled on the server, and DTEXEC returns immediately. DTEXEC does not wait for the package execution to complete. In this case, %ERRORLEVEL% would represent whether the package execution was created successfully, and … Continued
Queries for Data Flow Component Performance in SQL 2012
The built-in SSIS Catalog reports in SSMS show a breakdown of task and component performance of a package execution when you run with a logging level of “Performance”. For a more detailed or customized view, you can query the catalog tables directly. The key table to get data flow performance information is [catalog].[execution_component_phases]. Here are … Continued
Setting a Default Logging Level in the SSIS Catalog
One of the powerful features of the SSIS Catalog is that it can automatically capture logs for packages run on the server. The logging level is set to Basic by default, and can be changed when the package is run on the Advanced tab of the run UI (as shown below. This can also be … Continued