Extracting Data from SQL to CSV files using SSIS and PowerShell 7.x

 Objective

I decided to combine SSIS and PowerShell 7.0 to create one new ETL SSIS package that transfers CSV files to SQL server using a configuration table, the objective is to create a config table in SQL that contained all the information about an ETL like 

1 - Source SQL table

2 - Selected fields

3 - Filters like the t-sql WHERE clause 

4 - Tracking mechanism

5 - Destination File Path 

6 - Destination File name and extension

7 - etc...

.

Requirements

    The powershell script will read CSV files and insert the data into a SQL Server destiantion table, the parameters are as mentioned.

1. [String]$srcSQLServerConnectionString = "Server = ABC ; Database = XYZ; Integrated Security = True;"
2. [String]$srcSELECTQuery = "SELECT Fields  FROM tblXYZ"
3. [String]$desFilePath = "<DRIVER>:\...\ETLFolder\FileToBeSFTPed"
4. [String]$desFileName = "YYYY-MM-DD HHMMSS FileName"
5. [String]$desFileExtension = "csv"                                 OR "txt"
6. [String]$desColumnDelimiter = "TAB"                       # "TAB"   "`t"    |    ;    `

    At this point the SSIS package will read the config table, start a "Tracking mechanism" (getting the MAX identity field or MAX modified date) then create a final T-SQL string for the PowerShell.

            Some of the config table are as mentioned

    Source Fields 

1. [srcTableSchema] nvarchar(20)
2. [srcTableName] nvarchar(200)
3. [srcFieldList] nvarchar(2000)
4. [srcWHEREClause]                 nvarchar(1000)
5. etc...

    Destination Fields

1. [desFolder] nvarchar(2000)
2. [desFileName] nvarchar(50)
3. [desFileExtension] nvarchar(50)
4. ....
5. [srcPreExecute_usp] nvarchar(50)
6. [srcPostExecute_usp_Success] nvarchar(50)
7. ....

Please note…

• PowerShell script may need to be registered
• The SSIS has an internal logging system
• Three custom SQL stored procedures can be used/set within the configuration table for each ETL , one for PreETL, second PostETLSuccess and Finally PostETLFailure

Do you want the code? Click here (From Google).

Perspective Hierarchy - 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 perspective hierarchies 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 3.

1. Download the script file: 0386 PerspectiveHierarchy.sql
2. Run the script in SSMS.
3. Download the PowerShell script: 386--PerspectiveHierarchy.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 [PerspectiveHierarchy] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Perspective Column - 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 perspective columns 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 3.

1. Download the script file: 0384 PerspectiveColumn.sql
2. Run the script in SSMS.
3. Download the PowerShell script: 384--PerspectiveColumn.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 [PerspectiveColumn] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Perspective Table - 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 perspective tables 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 3.

1. Download the script file: 0382 PerspectiveTable.sql
2. Run the script in SSMS.
3. Download the PowerShell script: 382--PerspectiveTable.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 [PerspectiveTable] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Perspective - 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 perspectives 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 3.

1. Download the script file: 0380 Perspective.sql
2. Run the script in SSMS.
3. Download the PowerShell script: 380--Perspective.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 [Perspective] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

DM Database - Document and Track changes in your Tabular Model

Objective

Extract the metadata (properties) from a tabular database model using a PowerShell script and SQL Server 2016. In this section we are going to extract the list of the DM Databases.

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

1. Download the SQL script file: 0200 TabularDatabase.sql
2. Run the script in SSMS.
3. Download the PowerShell script 200--TabularDatabase.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, depending on how you configure the parameters.
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 [TabularDatabase] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Document and Track changes in your Tabular Model

Problem Statement

One of the biggest problems I have encountered was the inability to easily trace the variety of changes being made to my tabular model. Every developer was making and deploying changes to the model on the fly and I was having trouble tracking these down. On top of that, the client wanted me to document every single object within the tabular model, provide reports that can compare the changes between each deployment of the tabular model and so on...

Being able to accurately analyse a Data Model database (DM) is important for various business intelligence projects, such as: documentation, data lineage, metadata design changes, data growth and etc. In order to achieve this, you will need to extract the metadata objects from a DM and present them in a readable form, such as: SSRS, Power BI, or other business intelligence tool.

With SQL Server 2016 came the release of the tabular metadata object. The Tabular model decreases the amount of time required for designing and deploying.

Keeping this in mind, I said to myself, I must make a SQL database that will track the changes of each tabular metadata object. Basically, I need to extract the metadata of the tabular model or, in simple words, I need to make myself a "Metadata Dictionary".

NOTE: You can use Dynamic Management View (DMV) Queries to extract some of the objects. However, the DMV Queries do not provide the full breadth of the required information about the metadata.

What is a "Metadata Dictionary"?

A Metadata Dictionary is a series of SQL tables in SQL Server that will contain the metadata (schema properties) of your tabular DM. In this article, we will achieve this through use of SQL and PowerShell scripts.

What are the uses of a Metadata Dictionary?

1. Track/trace down the metadata changes after each deployment
2. Document each object in the system from a business and IT perspective (object naming, business naming, description, etc...)
3. "Translation" in SSAS Tabular (Multi-Language in SSAS)
4. Data lineage/flow for IT and Business groups.
5. Data type lineage from the Data Warehouse (DW) table fields up to the DM attribute.
6. BI auditing (Pre-auditing data before loading to the DW)
7. Incremental Partition Processing (incremental loading)
8. Evaluate/understand the design.
9. Business rules and requirements
10. Data growth
11. SSAS and SQL security. 

You can see the list of the Tabular objects after you have installed SQL Server Data Tools in Visual Studio (SSDT).

Do you know who TOM is?

Let me introduce you to TOM, the tabular object model.

Example Tabular Object Model hierarchy

System Requirements

In order to proceed with the solution mentioned below, you must have the following installed/configured in your environment:

1. SQL Server 2016 or higher.
2. SQL Analysis Services 2016 (Tabular)
3. At least one tabular model database within Analysis Services < If you are using AdventureWorks make sure it is set to, Version =13.0 and Compatibility Level =SQL Server 2016(1200) >
4. PowerShell (ISE its more visualize it helps more)
5. Create a SQL database to hold the tabular metadata information. In my example I have called it [ASTabularPropertyCollector].
6. Create 3 schemas in your database : [Tab], [Ppt] and [Dsv] (which stands for Tabular model, Perspective and Data Source View, respectively)

NOTE:  In order to confirm that you have what you need installed correctly, you may run this PowerShell script: 002--TestTheRegistry.ps1. For more information, please see: Install, distribute, and reference the Tabular Object Model.

Solution

I will provide you with everything you need to extract the tabular metadata information for each of the following. Each solution will extract the metadata from DM to DW. (I might add SSRS or PowerBI for visualization).

Special Note
Each solution I have provided contains:

1. A SQL script that creates the related SQL table - this script must be run first.
2. A PowerShell script that will extract the metadata from the DM and INSERT it into the SQL database ([ASTabularPropertyCollector])
1. The PowerShell script parameters need to be modified to fit your environment:
• $ASServer is your Analysis Service Server name (SSAS Instance Name)
• $SSASTabularDB is your Tabular Database name (Tabular DM database name)
• $SQLServer is your SQL Server name that contains the [ASTabularPropertyCollector] database
• $DBName is the name of the "[ASTabularPropertyCollector]" database
• $flgDisplayOnly, this is a flag that will allow you to see part of the extracted result in powershell ISE before inserting the data into the "[ASTabularPropertyCollector]", while testing use "TRUE" or else "FLASE" for a final insert
• $BatchLogID is a Batch Log ID for each Load of the Powershell scripts
2. Generally I use a *.bat file to load my PowerShell scripts and each time I change the $BatchLogID manually. 

Solution Breakdown

1. Databases (DM DB)
2. Tables
3. Columns (Fields)
4. Calculated Columns
5. Hierarchies
6. Table Annotation
7. Relationship
8. Perspective
9. Perspective Table
10. Perspective Relationship
11. Perspective Column
12. Perspective Hierarchies
13. Perspective Measures
14. Measures
15. KPI
16. Partitions
17. etc...

Conclusion

You should now be able to accurately and efficiently obtain the metadata from tabular model.

References

Tabular Models (SSAS)

Tabular Model Designer (SSAS)
Comparing Tabular and Multidimensional Solutions (SSAS)
https://msdn.microsoft.com/en-us/library/mt706505.aspx