Server

SELECT * From SSIS.DataFlow

SELECT * From SSIS.DataFlow

If you’ve been looking through the documentation for the Power BI preview, you might have noticed a section on Publishing SSIS Packages as OData Feeds. This functionality lets you create a T-SQL View over an SSIS data flow using a new SSIS add-on called the Data Feed Publishing Components. This add-on works with SQL Server … 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

What Events are Included in the SSIS Catalog Log Levels

Logging Levels Packages run through the SSIS Catalog can automatically have their logs captured in SQL Server 2012. The SSIS Catalog provides four different log levels – None, Basic, Performance, Verbose. The default log level is Basic. The table below shows which SSIS events are captured in the [catalog].[operation_messages] view in the four logging levels. … Continued