Server

Links from the SSIS Roadmap Session

Links from the SSIS Roadmap Session

Here are some of the resources I mentioned in the SSIS Roadmap session at the PASS Summit. SSIS Reporting Pack from Jamie Thomson   DQS Matching Transform from OH22 Data   DQS Domain Value Import Destination from OH22 Data There is also a great series of blog posts (part 1, part 2, part 3) on … Continued

Publish to SSIS Catalog using PowerShell

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

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

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