Saturday, 20 May 2017

Partition - Document and Track changes in your Tabular Model


Extract the metadata from a tabular model using a PowerShell script and SQL Server 2016. In this section, we are going to extract the list of Tabular partitions and their corresponding metadata.


Please see the requirements listed here: Document and Track changes in your Tabular Model


If you do not want to store metadata in a separate database, please proceed to step 4.
  1. Download the script file: 0500 Partition.sql
  2. Run the script in SSMS.
  3. Download the PowerShell script: 500--Partition.ps1.
  4. Use PowerShell ISE (or other PowerShell tool) to open the PowerShell file.
  5. Set the parameters in the PowerShell script to align with your environment (see Requirements).
    1. This script can either display the information or populate the table created in the step above.
  6. Run the PowerShell script (generally I use a *.bat file to load all of my PowerShell scripts).


Once the above steps have been completed, your SQL Server instance will have a table called [Partition] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

Querying this table should provide results similar to those seen below.


Thursday, 4 May 2017

Creating SSIS Package Components and Features Programmatically using .NET

Problem Statement

This might sound strange, but I am writing about a subject that has been around since 2005. However, since then, the objects within SSIS 2016 have changed and I recently had a customer request that prompted me to document my findings.

I needed to add and configure some features in an SSIS package and I had old .NET code that did the magic for me in 2007. I used the same code for my SQL 2016 SSIS package but it didn't work. WHAT?!?!


Some of the changes that were required included the following:
  1. OLE provider is now "Provider=SQLNCLI11.1"
  2. LogProvider object in SQL 2016 is no longer 2, it's 5: "DTS.LogProviderSQLServer.5"
    1. NOTE: As of when this blog post was written, this information could not be found on MSDN Online.
  3. And more...

Benefits of having objects in the package created programmatically

  1. The package framework allows for a standardized approach.
  2. Achieve more consistency in your SSIS packages created through package framework.
  3. Save time and be more accurate in designing the required changes.
  4. Understand the inner layer of SSIS package design.

Solution Breakdown

I am going to create or configure the below objects in a sample SSIS package
  1. Add a Connection Object
      Dim conMgrDest As ConnectionManager = Pkg.Connections.Add("OLEDB")
  2. Add Event Handlers
  3. Set the SSIS Logging
      Dim logProv As LogProvider = Pkg.LogProviders.Add("DTS.LogProviderSQLServer.5")
       Pkg.LoggingMode = DTSLoggingMode.Enabled 
       Pkg.LoggingOptions.EventFilter = arrayEventFilter


  1. Download the Sample Solution
  2. I have created 4 empty SSIS packages that are in the "SampleEmptySSISPackages" folder, in the case of my customer I had 300 packages.
  3. Copy all packages into the "ToBeProcessed" folder.
  4. Open the solution, "DotNetCodeOnSSISPkgs.sln".
  5. Open "pkgFwDotNetCodeOnSSISPkgs.dtsx" and go to Parameters.
  6. Make sure that the "uPara_FolderPath" is pointing to the location of your "ToBeProcessed" folder.
  7. To create the Connection Manager, you will need to set the "SSISLogServer" and the "SSISLogDB".
  8. Run the SSIS package

Validating Results

To check the results, open the packages in the folder "ToBeProcessed" in Visual studio. You will see the changes mentioned below:
  1. New Connection Object "conn_OLEDB_SSISLoggingConnection" is added
  2. The Event Handlers: OnError, OnPostExecute and OnPreExecute events have been added at the package level.
  3. In the menu SSIS --> Logging, you will see the following items:
    1. Containers: Package is checked
    2. Providers and logs: new name has been added, "My SSIS Log Provider for SQL Server", and the same connection object has been used.
    3. Details: OnError, OnExecuteStatusChanged, OnPostExecute, OnPreExecute are all checked
NOTE: To do the test again, you must copy the sample files from the "SampleEmptySSISPackages" folder into the "ToBeProcessed" folder and rerun the "pkgFwDotNetCodeOnSSISPkgs.dtsx" package.