Partition - Document and Track changes in your Tabular Model

Objective

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.

Requirements

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

Steps

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).

Conclusion

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.

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?!?!

Solution

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
     Pkg.EventHandlers.Add("OnError")
3. Set the SSIS Logging
     Dim logProv As LogProvider = Pkg.LogProviders.Add("DTS.LogProviderSQLServer.5")

       Pkg.LoggingOptions.SelectedLogProviders.Add(logProv)  

       Pkg.LoggingMode = DTSLoggingMode.Enabled 

       Pkg.LoggingOptions.EventFilter = arrayEventFilter

Steps

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.

References

MSDN LogProviders.Add Method
Adding Connections Programmatically