Tag Archives: SSAS

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    11 Things you should know about Biml – an overview (Datawarehouse Series)

    Intended audience and purpose of this post

    This post gives an overview of a number products and features related to Biml on a high level.
    Therewith it is a starting point for further learning and investigating.


    Biml means Business Intelligence Markup Language.
    What else do you need to know about it, if you are a professional worker in the business intelligence and data warehouse domain?


    11 Things you should know about Biml – an overview

    1. Who invented Biml?
    2. What is Biml?
    3. What are BimlExpress and BIDS Helper?
    4. What is BimlScript?
    5. What is BimlFlex?
    6. What are Mist and BimlStudio?
    7. What are BimlBundles?
    8. What are BimlTransformers?
    9. Is the free part of Biml a way to get me addicted to it?
    10. How do I know that Biml is a proven technology that is going to stay?
    11. Where can I find more information on the internet?

    1. Who invented Biml?

    Biml and all tools around it is created by Varigence, a company with CEO Scott Currie, a former Microsoft Employee.

    Back to top

    2. What is Biml?

    Biml – the markup language – is an XML dialect to describe Microsoft SSIS Packages and SSAS Cubes.
    Everything you can do with SSIS, you can do with Biml (quote of Scott Currie, done during the Biml usergroup NL kickoff on May 7, 2016).
    The goal of Biml is to improve productivity of Data warehouse development.
    But the biml ‘ecosystem’ consists of more than only the Markup Language. Other components are:

    • Biml Script
    • BimlExpress and BIDS Helper
    • BimlFlex
    • BimlStudio (also/formerly known as Mist)
    • BimlBundles
    • BimlTransformers

    Back to top

    3. What are BimlExpress and BIDS Helper?

    BimlExpress and BIDS Helper both are Add-ins for Business Intelligence Development Studio (actually the Visual Studio version with the project types needed for Business Intelligence).
    BimlExpress is the successor of BIDS Helper and one of it biggest advantages is syntax highlighting and intellisense.
    It is really worth checking them out, because they can improve your productivity, especially when you use metadata and design patterns for SSIS packages.
    You can download and install BimlExpress. You have to register but can use the product at no cost.

    Back to top

    4. What is BimlScript?

    BimlScript is what makes Biml (the markup language) even more interesting. With BimlScript you can write .NET Code (C#) and do all kind of interesting stuff, like accessing your metadata, using for each loops, and generate SSIS packages using a design pattern.
    I have generated packages to load dimensions of a star schema, and I intend to blog about it later this year in more detail.

    Back to top

    5. What is BimlFlex?

    BimlFlex is a datawarehouse automation framework of Varigence. It can greatly improve productivity when building data warehouses.
    It uses Predefined patterns for data warehouse automation and Extension Points that you can use to change the standard automation patterns on certain ‘change points’, or can build exceptions for the pattern of this one very special table.
    BimlFlex is part of BimlStudio and therefore a commercial (paid) product.

    Back to top

    6. What are Mist and BimlStudio?

    Mist and BimlStudio are the same tool. BimlStudio is just the new name, due to strange associations with the “Mist” name in different languages, for example in German (What does the German word “Mist” mean?). This is why they changed the name.
    Also the names for all products and techniques associated with Biml now start with Biml, which makes them more recognizable.
    BimlStudio has a number of extra features when compared with BimlExpress.
    BimlStudio is a paid product, you can buy a perpetual license or a monthly subscription.


    Back to top

    7. What are BimlBundles?

    BimlBundles are part of BimlStudio and protect your source code while your customer still can make changes at framework extension points.
    Koos van Strien did a very good job at explaining BimlBundles in more detail here.

    Back to top

    8. What are BimlTransformers?

    With BimlTransformers you can define and store changes on Biml code, instead of changing the Biml code itself. If you have worked with Visual Studio/.NET before, you can compare it with changes you make for your config file for different environments.
    Koos van Strien did a very good job at explaining BimlTransformers in more detail here.

    Back to top

    9. Is the free part of Biml a way to get me addicted to it?

    Nope. Although Varigence works sales model is a Fremium model (a free product that can be extended/enhanced with paid products), the promise of Scott Currie, done during the Biml usergroup NL kickoff on May 7, 2016, is that all features that are released for free will stay for free. You can count on that.

    Back to top

    10. How do I know that Biml is a proven technology that is going to stay?

    Biml already exists for about 8 years and has proven itself already. Varigence is a global player and steady company that does not suddenly disappear. I am not concerned if Biml will exist 10 years from now, and you should not have to be either. Even in the utterworst case, you would still have the SSIS packages that Biml has generated for you, and you can edit/maintain them without Biml.

    Back to top

    11. Where can I find more information on the internet?

    To read more about Biml, here are a few interesting web sites, blog posts and Linked In Usergroups:

    Back to top

    Conclusion / Wrap up

    Biml is here to stay and adds a lot of value, even if you use only the free parts of it. Varigence can only be praised for that.
    In this blog post I shortly described a number of products and techniques that are part of the Biml ‘ecosystem’.
    I do not have a commercial interest in promoting Biml, I am just a Business Intelligence Consultant who likes Biml a lot.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    A Plug and Play Logging Solution (SSIS Series)


    Ever had to investigate an error that occurred during execution of a SSIS package? Then you will know that logging is the key to know what happened.

    And not only logging in SSIS itself, but also in stored procedures, c# script tasks, c# console applications you might have written and are executed from within the package.
    So the log tells you a story (I once heard “so it reads like a thrilling boys’ book”, I liked that expression, GJ).

    Also you do not want to reinvent the wheel for every SSIS project, but want to use a standard solution. Plug and Play instead of spending sprints on “utitilies”.

    Feature list

    The features in short (as compared with what SSIS offers out of the box):

    1. It’s a Plug And Play SSIS Logging Solution in most cases: no additional programming in SSIS Packages is required*), so can be easily used for all existing packages! Only when you have multiple master packages with the same name, you will have to do a little of set up work.
    2. Log messages are labeled with a message class (a sort of severity of the message) for easy filtering.
    3. Changing the maximum message class to log is possible without redeploying any SSIS Packages (for instance enable VERBOSE logging for trouble shooting, then set it back to DEBUG or INFO).
    4. Log messages are labeled with a retention class for easy purging. There is also an “audit” retention class for messages that may never be purged/deleted.
    5. Purging can be automated, so no scheduling is needed.
    6. Logging is Integrated/centralized for SSIS Packages, stored procedures and other home made software.
    7. Deduplicated logging can be enabled, if this is done, identical SSIS logging messages (e.g. first from the OLE DB Destination, then from the Data Flow Task, and then from the SSIS Package) are logged only once as much as possible.
    8. Master / child relations for SSIS packages can be configured, so that log entries from master- and childpackages can be retrieved easily together in a single timeline. Out of the box this works using a naming convention (Master package must have “master” in the name) but can be fine tuned for every package that runs.
    9. Easy configuration by executing a stored procedure, to set retention periods, maximum log class, automatic purging on/off, deduplicated logging on/off, and naming convention for the master package.
    10. Storage of both start- and endtimes of the current timezone as well as UTC start- and endtimes.

    *) As far as the logging that SSIS does is concerned. If you want logging in your own stored procedures and executables you need to implement it first.

    Quick Start Guide

    1. Download the installation solution script here.
    2. Execute the entire script to install on new database [logdb]. To install on existing database, skip the part of the script where the database is created and execute the rest against an existing database. All objects the solution creates are created in database schema [log] (except for stored procedure [dbo].[sp_ssis_addlogentry], this name is needed by SSIS).
    3. Enable logging in your SSIS Packages if you have not already done that. Use a logging provider for SQL Server and select all events for all components of the package (more details below).
    4. Configure the connection managers of your SSIS Packages to use the database in which you installed the solution. Alternatively you can execute the stored procedure [log].[spCreateCrossRefs] to redirect logging for existing packages to the logging solution without any changes in your SSIS packages. See the User Manual for more details.

    Directly to User Manual

    Steps to follow when you want to enable logging in your SSIS Package:


    User Manual

    Table Of Contents


    The “hansmichiels.com Plug And Play Logging Solution” provides Plug and Play logging for SSIS Packages, stored procedures and other home made software, like command line applications or web applications.
    After installing the solution, as explained in the Quick Start Guide, you can manage the solution using a few stored procedures, or accept the default settings and run it out of the box.

    Understanding MessageClass and RetentionClass

    MessageClass can have one of these 6 values:

    • 1 = Fatal Error (SSIS OnError event)
    • 2 = Non-fatal error (reserved for user defined logging for non-fatal errors, not used by SSIS packages)
    • 3 = Warning (SSIS OnWarning event)
    • 4 = Info message (SSIS: PackageStart, PackageEnd, OnInformation events)
    • 5 = Debug message (SSIS: OnPreValidate, OnPostValidate, OnPreExecute, OnPostExecute events)
    • 6 = Verbose messages (SSIS: Pipeline, Diagnostic and OnProgress events, typically more than 90% of all SSIS log messages)

    RetentionClass aligns with MessageClass because it also has values 1 to 6 and by default the RetentionClass will be equal to the MessageClass. However, when you do custom logging in your own stored procedures or executable programs, you can provide a different value for it, for instance MessageClass 4 (INFO) but RetentionClass 1 if you want the message to be kept longer than the value for RetentionClass 4.

    In addition there is a RetentionClass 0 for messages that may never be purged/deleted.

    There are two ways to prevent that log messages are purged/deleted:
    1. By setting the configuration setting @RetentionPeriodClassN (where N is from 1 to 6) to 0. Then ALL messages for that RetentionClass will never be purged. However when the configuration setting is changed to a different value, the next time spPurgeLog is executed, the messages can still be deleted.
    2. By explicitly adding log entries with RetentionClass 0. Those entries do not depend on configuration settings, and are never deleted, unless you delete them directly from the [log].[Entry] table.

    Back to Table of Contents

    Understanding the stored procedures and user defined function of the solution

    Stored procedure [log].[spConfigure]

    With this stored procedure you can change a number of configuration settings which are parameters of the stored procedure.

    Configuration: a child can do the laundry

    You only have to provide values for the configuration settings (parameters) that you want to change. Other settings will remain unchanged.

    You can set the following parameters:

    • @ExecutionGuid: Guid used for logging the configure operation. Can be NULL.
    • @MaxMessageClass: The maximum MessageClass to log. For instance 5 means that log entries with MessageClass=6 (VERBOSE) are not logged. The defaultvalue is 5 (DEBUG).
    • @RetentionPeriodClass1: Hours to keep a log entry if the [RetentionClass] column has value 1. Set the value to 0 if the log message may never be deleted. The defaultvalue is 0.
    • @RetentionPeriodClass2: The same for [RetentionClass] = 2. The defaultvalue is 0.
    • @RetentionPeriodClass3: The same for [RetentionClass] = 3. The defaultvalue is 4320 hours or 180 days.
    • @RetentionPeriodClass4: The same for [RetentionClass] = 4. The defaultvalue is 2160 hours or 90 days.
    • @RetentionPeriodClass5: The same for [RetentionClass] = 5. The defaultvalue is 336 hours or 14 days.
    • @RetentionPeriodClass6: The same for [RetentionClass] = 6. The defaultvalue is 168 hours or 7 days. Verbose messages are typically more than 90% of all messages, so a short retention period is recommended, if logged at all.
    • @DefaultMasterSourcePattern: default naming convention for master packages. Use wildcard % for nameparts. The defaultvalue is ‘%Master%’.
    • @EnableAutoPurgeOnPackageEnd: If enabled, spPurgeLog will be executed every time a SSIS Package ends. This keeps the log clean without an additional effort for scheduling, but the downside is that spPurgeLog might be executed more often than when it is scheduled daily. This is not harmful, but could have a negative impact on package performance. The defaultvalue is 0 (false).
    • @EnableDeduplicatedLogging: If enabled a staging table [log].[EntryStaging] will be created and duplicate SSIS log messages (with different sources) will be deduplicated as much as possible. Still in some situations duplicates may occur but a lot less than when this setting is not enabled. The defaultvalue is 0 (false).
    • @ResetAllToDefaultValues: Use 1 to reset all values to the values of the DEFAULT constraints on the [log].[Config] table. In that case all other parameter values are ignored.

    Examples of use:

    EXECUTE [log].[spConfigure] @ResetAllToDefaultValues = 1;

    EXECUTE [log].[spConfigure]
    @EnableDeduplicatedLogging = 1,
    @EnableAutoPurgeOnPackageEnd = 1,
    @MaxMessageClass = 6,
    @RetentionPeriodClass2 = 8640,
    @RetentionPeriodClass6 = 48;

    Back to Table of Contents

    Stored procedure [log].[spAddLogEntry]

    You can use this stored procedure to implement logging in your own stored procedures or executable programs.

    It is advised that your own stored procedures which are called from within SSIS Packages, have a parameter @ExecutionGuid (with datatype UNIQUEIDENTIFIER). Provide the value from the package variable System::ExecutionInstanceGUID.

    You can set the following parameters:

    • @Event: The log event, can be NULL. If you leave it empty, the event name will be based on the value for @MessageClass.
    • @Source: The log source.
    • @SourceGuid:The log source guid, can be NULL.
    • @ExecutionGuid: A global unique identifier to be able to distinct log messages of different jobs running in parallel.
    • @StartTime: Can be NULL, if NULL the current date/time is used.
    • @EndTime: Can be NULL, if NULL the value of @StartTime is used (which can be set to current date/time).
    • @MessageClass: The MessageClass of the message: 1=Fatal Error, 2=Error, 3=Warning, 4=Info, 5=Debug, 6=Verbose
    • @RetentionClass: A numeric indicator for the time after which the log messages can be deleted or purged. Values align with MessageClass, and in addition there is a RetentionClass with value 0 for log messages that may never be deleted, e.g. for auditing purposes. Can be NULL, if NULL the value provided for MessageClass will be used.
    • @Message: The log message.

    Examples of use:

    — At the beginning of a stored procedure:
    DECLARE @Source NVARCHAR(1024) = ‘log.spCreateCrossRefs’;
    DECLARE @Message NVARCHAR(2048) = ‘Starting: ‘ + @Source;
    EXECUTE [log].[spAddLogEntry] @Message = @Message, @ExecutionGuid = @ExecutionGuid,
    @Source = @Source, @MessageClass = 4;

    — at the end of a stored procedure:
    SELECT @Message = ‘Finished: ‘ + @Source;
    EXECUTE [log].[spAddLogEntry] @Message = @Message, @ExecutionGuid = @ExecutionGuid,
    @Source = @Source, @MessageClass = 4;

    — To log something specific:
    SELECT @Message = ‘Purged ‘ + CAST(@RowCount AS NVARCHAR) + ‘ log entries with RetentionClass ‘ + CAST(@RetentionClass AS NVARCHAR) +‘.’;
    EXECUTE [log].[spAddLogEntry] @Message = @Message, @ExecutionGuid = @ExecutionGuid,
    @Source = @Source, @MessageClass = 4, @RetentionClass = 0;

    Back to Table of Contents

    Stored procedure [log].[spPurgeLog]

    Schedule the execution of this stored procedure daily when you do not want to set EnableAutoPurgeOnPackageEnd to 1 (true).

    This stored procedure has a parameter @ExecutionGuid used for logging the purge operation, but you can omit the parameter, then a new unique identifier value will be used.

    Example of use:

    EXECUTE [log].[spPurgeLog]

    Back to Table of Contents

    Stored procedure [log].[spCreateCrossRefs]

    Stored procedure to create synonyms in a database and create or replace the stored procedure [dbo].[sp_ssis_addlogentry] (hereafter collectively called: the cross references)

    Use this stored procedure for two purposes:

    1. If you want to do logging in your own stored procedures in the database specified with parameter @DatabaseName
    2. If you want to redirect logging for SSIS Packages that use the database specified with parameter @DatabaseName for logging. In this way those packages will use the logging solution without the need to change the connection string.

    You can set the following parameters:

    • @ExecutionGuid: Guid used for logging the configure operation. Can be NULL.
    • @DatabaseName: the name of the database in which the cross references must be created.
    • @DebugMode: if 1, more details will be shown in the output window
    • @SimulationMode: if 1, only the SQL Statements are shown in the output window, but they are not executed.
    • @DropOnly: if 1, the cross references are only dropped, but not (re)created.

    Example of use:

    USE [logdb]
    EXECUTE [log].[spCreateCrossRefs]
        @DatabaseName = 'MyOtherDb'
      , @DebugMode = 1
      , @SimulationMode = 1
      , @DropOnly = 0

    Back to Table of Contents

    Stored procedure [log].[spSetMasterSourcePattern]

    While packages run, the table [log].[Source] is filled with Source (= SSIS Package) and MasterSource information, based on the configuration setting @DefaultMasterSourcePattern.

    If the value for @DefaultMasterSourcePattern is correct for all packages that run, you do not have to change anything.

    However when the Master/child Source relations need to be changed, you can use stored procedure [log].[spSetMasterSourcePattern] to configure master- and child package relationships.

    The definition of a master package is a package that executes another SSIS Package, the child package. When going more than one level deep, only the ultimate parent package (the one that starts everything else) has to be defined, the “package tree” is not relevant. For instance Package A executes Package B, and Package B executes Package C. In this situation A should be defined as master package for C.

    You can set the following parameters:

    • @ExecutionGuid: Guid used for logging the configure operation. Can be NULL.
    • @Source: The log source or log source pattern. For a pattern use a % wildcard, e.g. “Load%” for all sources of which the name starts with “Load”.
    • @SourceGuid: Source Guid, if NULL Source will be retrieved with the value of @Source only.
    • @MasterSourcePattern: The master source to use for this @Source/@SourceGuid
    • @MasterSourceGuid: Master Source Guid, if NULL the connection to the Master Source will be made using only the @MasterSourcePattern. You only need to configure @MasterSourceGuid, if you have multiple master packages in different projects with the same name (e.g. all called “Master.dtsx”) that can run at the same time. In that situation you have to link child- to masterpackages using both @MasterSourcePattern and @MasterSourceGuid.

    The easiest way to configure your master- and childpackages is to use spConfigure to set @DefaultMasterSourcePattern so that it matches your masterpackage name.
    Then run your masterpackage once. In this way the table [log].[Sources] is already filled with all executed (master- and child)packages, so you do not have to do that yourself.
    Then use the examples below to finetune, if necessary.
    Examples of use:

    ---) Set MasterSourcePattern for all sources (use only after SSIS Packages have run for the first time).
    EXECUTE [log].[spSetMasterSourcePattern]
      @Source = '%', @MasterSourcePattern = 'Master%'

    -- Then undo for the master sources themselves
    EXECUTE [log].[spSetMasterSourcePattern]
      @Source = 'Master%', @MasterSourcePattern = NULL

    ---) Set MasterSourcePattern for one source.
    -- Without SourceGuid (use only after SSIS Packages have run for the first time).
    EXECUTE [log].[spSetMasterSourcePattern]
      @Source = 'LoadDimCustomer', @MasterSourcePattern = 'DWHMaster'

    -- With SourceGuid and MasterSourceGuid (can be used prior to running any packages).
    EXECUTE [log].[spSetMasterSourcePattern]
      @Source = 'LoadDimCustomer',
      @SourceGuid = '7480D07D-5099-4B76-9404-17C2AFD0E603', -- PackageID property of SSIS Package.
      @MasterSourcePattern = 'Master',
      @MasterSourceGuid = '0F429F36-784D-4F05-8D5A-9374609A32B9' -- PackageID property of your master SSIS Package.

    Back to Table of Contents

    Stored procedures [log].[spOnExecutionStart] and [log].[spOnExecutionEnd]

    Used internally by the logging solution.

    Back to Table of Contents

    User defined function [log].[fnLogEntry]

    User defined function to select log entries.

    This function has the following parameters:

    • @Source: Packagename to select log entries for (including log entries of subpackages). Can be NULL. If NULL then the last execution of any Package will be selected.
    • @LastXExecutions: A number to indicate for how many executions you want to see the log. Defaultvalue is 1.
    • @MaxMessageClass: Filter on MessageClass, for instance use 2 to show only log messages with a MessageClass less than or equal to 2. Defaultvalue is 6.
    • @IncludeRetention0: If 1, also messages with RetentionClass 0 (never delete) will be shown, regardless of their MessageClass.

    Example of use:

    SELECT * FROM [log].[fnLogEntry] (‘Master’, 1, 4, 1 )

    Back to Table of Contents

    How to implement logging in own executables

    Logging in own executables (for instance C# Console Applications) is fairly easy to set up.
    Keep the following in mind:

    • Generate a new guid every time the executable runs, and use it as value for @ExecutionGuid.
    • Assign the executable a persistent SourceGuid, and use it as value for @SourceGuid.
    • Use the executable name as value for @Source. While the executable runs, you can use lower level names (e.g. class and/or method names) for @Source, but still use the executable @SourceGuid, and not a different one.
    • When the executable starts, use the stored procedure spAddLogEntry to add an entry with @event=”ExecutionStart”. This triggers built in facilities of the logging solution.
    • Just before the executable stops, use the stored procedure spAddLogEntry to add an entry with @event=”ExecutionEnd”.
    • When the application is called from within a SSIS Package, use spSetMasterSourcePattern to create a master/child relationship.
    • If you are interested in a C# code example just let me know, and I will dedicate a future blog post to it.


    Download the logging solution script here.

    How about updates?

    I intend to update the solution when needed.
    If you find bugs or have interesting additions, I would be thankful if you can share those with me so I can improve the logging solution.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.