Michał PawlikowskiMultidimensional, SSAS

“Internal error: An unexpected exception occurred.” when trying to filter simple dimension that has ragged parent-child hierarchy in multidimensional model.

Platform: SQL Server Analysis Services 2016 RTM and later (Multidimensional)   In one of our projects, we encountered an interesting problem related to parameter filtering on one of our reports (SSRS). However, we would like to make sure that no mistake has been made before it hits as a bugCzytaj dalej / Read more

Michał PawlikowskiAzure, Azure Data Lake Storage Gen2, Azure Storage Explorer

Uploading files to Azure Data Lake Storage Gen2 from PowerShell using REST API, OAuth 2.0 bearer token and Access Control List (ACL) privileges

  Introduction In my previous article “Connecting to Azure Data Lake Storage Gen2 from PowerShell using REST API – a step-by-step guide“, I showed and explained the connection using access keys. As you probably know, access key grants a lot of privileges. In fact, your storage account key is similarCzytaj dalej / Read more

Michał PawlikowskiAzure Databricks

How to get Databricks WorkspaceID from REST API using PowerShell?

Getting information about ID of the Azure Databricks workspace is not so obvious. There is no information about it in the API specification: https://docs.databricks.com/api/latest/workspace.html That’s probably because this property is not related to the content of the workspace 🙂 That’s right! Raw JSON responses are intended to deliver different setCzytaj dalej / Read more

Michał PawlikowskiAzure, Azure Data Lake Storage Gen2, Azure Storage Explorer

Connecting to Azure Data Lake Storage Gen2 from PowerShell using REST API – a step-by-step guide

    Introduction Azure Data Lake Storage Generation 2 was introduced in the middle of 2018. With new features like hierarchical namespaces and Azure Blob Storage integration, this was something better, faster, cheaper (blah, blah, blah!)  compared to its first version – Gen1. Since then, there has been enough time toCzytaj dalej / Read more

Michał PawlikowskiAzure, Azure Automation, Azure Data Factory

Azure Data Factory V2 and Azure Automation – Running pipeline from runbook with PowerShell

  Introduction Azure Automation is just a PowerShell and python running platform in the cloud. In marketing language, it’s a swiss army knife 😛 Here how Microsoft describes it: “Azure Automation delivers a cloud-based automation and configuration service that provides consistent management across your Azure and non-Azure environments. It consists of process automation,Czytaj dalej / Read more

Michał PawlikowskiAzure, Azure Data Factory

Azure Data Factory V2 – Incremental loading with configuration stored in a table – Complete solution, step by step.

Introduction Loading data using Azure Data Factory v2 is really simple. Just drop Copy activity to your pipeline, choose a source and sink table, configure some properties and that’s it – done with just a few clicks! But what if you have dozens or hundreds of tables to copy? AreCzytaj dalej / Read more

Michał PawlikowskiUseful Scripts

TSQL query for generating a report from SSISDB with SSIS package processed in execution

And the thanks goes to Michał Powaga for providing me that script below 😉

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
USE SSISDB;
GO
 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
DECLARE @execution_id BIGINT = 70660;
-- your package id
 
 
WITH msgs
AS ( SELECT event_message_id ,
            execution_path ,
            package_name ,
            package_path_full ,
            event_name ,
            message_source_name ,
            package_path
     FROM   internal.event_messages (NOLOCK)
     WHERE  event_name IN ( 'OnPreExecute', 'OnPostExecute' )
            AND operation_id = @execution_id ) ,
     running
AS ( SELECT *
     FROM   msgs o
     WHERE  o.event_name = 'OnPreExecute'
            AND NOT EXISTS (   SELECT *
                               FROM   msgs AS c
                               WHERE  c.event_name = 'OnPostExecute'
                                      AND c.execution_path = o.execution_path ))
SELECT   ex.execution_id ,
         ex.project_name ,
         e.executable_id ,
         e.executable_name ,
         e.package_name ,
         e.package_path ,
         CONVERT(DATETIME, es.start_time) AS start_time ,
         CONVERT(DATETIME, es.end_time) AS end_time ,
         CONVERT(VARCHAR, DATEADD(ms, es.execution_duration, 0), 108) AS 'duration_h:m:s' ,
         es.execution_duration AS 'execution_duration_ms' ,
         es.execution_result ,
         CASE es.execution_result
              WHEN 0 THEN 'Success'
              WHEN 1 THEN 'Failure'
              WHEN 2 THEN 'Completion'
              WHEN 3 THEN 'Cancelled'
         END AS execution_result_description ,
         es.execution_path ,
         r.*
FROM     catalog.executions ex (NOLOCK)
         JOIN catalog.executables e (NOLOCK) ON ex.execution_id = e.execution_id
         JOIN catalog.executable_statistics es (NOLOCK) ON e.executable_id = es.executable_id
                                                  AND e.execution_id = es.execution_id
         FULL OUTER JOIN running r (NOLOCK) ON es.execution_path = r.execution_path
WHERE    e.execution_id = @execution_id
ORDER BY es.execution_id DESC;
 
 
 
 
WITH msgs
AS ( SELECT event_message_id ,
            execution_path ,
            package_name ,
            package_path_full ,
            event_name ,
            message_source_name ,
            package_path
     FROM   internal.event_messages (NOLOCK)
     WHERE  event_name IN ( 'OnPreExecute', 'OnPostExecute' )
            AND operation_id = @execution_id ) ,
     running
AS ( SELECT *
     FROM   msgs o
     WHERE  o.event_name = 'OnPreExecute'
            AND NOT EXISTS (   SELECT *
                               FROM   msgs AS c
                               WHERE  c.event_name = 'OnPostExecute'
                                      AND c.execution_path = o.execution_path ))
SELECT *
FROM   running;