Facts and Measures

Facts and Measures

Measures are useful numeric values originating from business transactions, which are used for analysis through aggregation later on.

They are also known as facts, so fact tables are basically collections of keys and measures.

Depending on the type, some facts are more useful for aggregation and analysis than others.

So how can you identify the type of measure you are collecting?

Here’s a quick general guide for confirming what type of fact you’re working with:

Questions to practice:

What are measures used for?

What are the different types of measures?

Can you think of any examples for each of the types?


Detect package runtime in SSIS

In this article we’ll be looking at how to add a simple runtime detection component to your SSIS packages. Although it’s possible to query the use32bitruntime value in the SSISDB catalog.executions table with an Execute SQL task, this only works for packages that have been deployed to IS Server. It’s better use a more universal solution that isn’t going to depend solely on the project deployment model.

The best way to detect the runtime of a package execution is using a script task. After the runtime has been detected, precedence constraints can be used to tell SSIS what to do in the case of 32-bit, or 64-bit execution.

In this example, the solution will end up looking like this:

Continue reading “Detect package runtime in SSIS”

Viewing the SSISDB Structure Easily in T-SQL

The following view will allow you to view the entire SSISDB structure easily, which is useful particularly when confirming the presence of certain folders, projects and packages from an SSIS Execute SQL Task.

Make sure you’re connected to the SSISDB database first before you run this code.

IF OBJECT_ID(N'catalog.structure', N'V') IS NOT NULL
  DROP VIEW catalog.structure;
CREATE VIEW catalog.structure
       , F.name AS [folder_name]
       , PR.project_id
       , PR.name AS [project_name]
       , PA.package_id
       , PA.name AS [package_name]
       , CASE
         WHEN PA.version_major IS NOT NULL THEN
         END AS [package_version]
  FROM catalog.folders AS F
    LEFT OUTER JOIN catalog.projects AS PR
      ON F.folder_id = PR.folder_id
    LEFT OUTER JOIN catalog.packages AS PA
      ON PR.project_id = PA.project_id;
FROM catalog.structure;

Project & Package Deployment Methods – SSIS Basics

As a newbie to SSMS and SSIS, one of the hardest things to wrap my head around was the difference in the way in which projects and packages are deployed.

After diving into Books Online, as well as playing around with SSIS endlessly, I came up with the chart below, which illustrates some of the differences between the project and package deployment models.



When a project is built, all of the files in the project are stored in an .ispac file, which is basically an archive containing all of the package files (.dtsx), project-level parameter and connection files, and a few others.

When a package is built, the package files are stored separately as .dtsx files, and a Deployment Utility file (.manifest) is also generated if the CreateDeploymentUtility option is set to True.

UI-based deployment method

Projects are deployed with the Integration Services Deployment Wizard, which can be initiated by either right-clicking the project in SSDT and selecting Deploy, or by double-clicking the .ispac file stored from the build. The first method will generate a new .ispac file to work with anyway.

Packages are deployed with the Package Installation Wizard. This can be accessed by double-clicking on the .manifest file generated from a build. The packages can then be deployed to either a file system location, or SQL Server, in the SSIS Package Store.

(Automated) Command line deployment method

For projects, use the following command line:

ISDeploymentWizard /S /SP:"MyProject.ispac" /DS:"MYSERVER" /DP:"/SSISDB/MyFolder/MyProject"

When the /S[ilent] option is not used, the UI version of the wizard will appear. It’s a good idea to first use the UI version to generate a command line to work with, which is displayed on the last page of the wizard.

The options for this method are as follows:


For packages, use the following command line.

dtutil /File DimCustomers.dtsx /COPY DTS;MSDB\DimCustomers

The above line deploys a package from the file system, to MSDB in the SSIS package store. The following code is an alternative that achieves the same thing:

dtutil /File DimCustomers.dtsx /COPY SQL;DimCustomers

A lot of abbreviation can be made with the options, so it pays to memorize what they all mean:


For more examples of how to use dtutil, be sure to take a look at books online.

Try using the methods mentioned above, and let me know how you go!


Alerts and Notifications in SSIS

An alert enables an operator or operators to receive notifications when a certain event occurs within SQL Server, such as a query error, SSIS package failure, or backup completion.There are three different methods of notifications, as follows:

  1. Database Mail
  2. Pager (for which support will be removed in the future, but is still supported for anyone stuck in 1994)
  3. Net send (still supported but this feature cannot be used for any OS after Windows XP Service Pack 2)2000px-crystal_clear_app_email-svg

Hmm, so there’s really only one method of notification which you should use: the Database Mail notification.

There are three ways in which you could use notifications with SSIS packages:

  1. The Send Mail Task, typically in an Event Handler (SMTP server will be required to set this up)
  2. The Notify Operator Task, which is similar to the Send Mail Task, but allows you to select the operators in SQL Server Agent to be notified
  3. The Execute SQL Task, using the sp_notify_operator command, is basically the same thing as the Notify Operator Task, but this method allows greater flexibility.


Logging options for SSIS

There are several logging options available for SSIS packages, and it can be difficult to know which is the best one to use. Let’s take a closer look at the different SSIS logging options, and what they can do.

SQL Server log provider saves log events into the sysssislog table in the msdb database by default, or in a database which you specify. The table can then be shared between users who can access the database.

Text files log provider stores the same information as the SQL Server log provider into a text file. This could easily be emailed to another user, or imported to a database or Excel spreadsheet later.

XML files log provider allows you to save the information as an XML file, which can add portability to the log data across different platforms, and also allow easy reading which viewed in an XML viewer or certain web browsers.

SQL Server Profiler, when used in SSIS, provides the same information as the other log providers, but saves the log events as a trace file so that it can then be correlated with Performance Monitor. This is a great way to monitor performance issues and pinpoint drags on performance. (See this great article by Brad McGehee for more info)

Windows Event Log log provider stores event information in Windows Event Viewer, which can then be used by system administrators. I feel this method is a little tedious when it comes to analysis, but some might find it useful.

For performance related diagnostics, I would store the log events in a trace file, otherwise I’d stick with the sysssislog table.

However, the log provider that is best for you will of course depend on the situation and how the log information will be used.