Sort on multiple columns Power BI

 Hi All,

To be able to apply sort on multiple columns in Power BI on Power BI desktop

1. Apply desired sort on first column

2. Then hold the shift key and apply sort on the second column 

3. Hold shift key and apply required sort on next column

Bingo...data is sorted

Power BI connecting to Azure Gen 2 - Access to this resource is forbidden

 Hi All,

While connecting from Power BI to Azure Gen 2 we encounter the below error 

Access to this resource is forbidden

To resolve this issue follow below steps

1. Check if Storage Blob Reader access is provided

2. In Power BI, click on options, clear permissions for already exisiting connection and then try again

This time sign in 

3. If you still get the same error, log on to the container using Azure storage explorer and the get the dfs url and not the windows.net url

Bingo issue fixed !!

Azure sql  Bulk insert error 

Issue - 

Cannot bulk load because the file could not be opened. Operating System Error Code 5

Analysis

I followed below steps to do Bulk load but was getting above error

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test1223$';

CREATE DATABASE SCOPED CREDENTIAL blobcred1

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2022-12-28T18:26:28Z&st=2022-12-15T10:26:28Z&spr=https&sig=PLZSqBdl%2BHAu5eu0xMSKsy8sc3cQ%2FRKVh3JAX3y8amQ%3D'

CREATE EXTERNAL DATA SOURCE CustomerDS4

    WITH (

        TYPE = BLOB_STORAGE,

        LOCATION = 'https://loyaltyhyperscale.blob.core.windows.net/loyalty',

        CREDENTIAL = blobcred1

    );

BULK INSERT [dbo].[CustomerTable] FROM 

'Customer.csv' 

WITH (DATA_SOURCE = 'CustomerDS4', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Resolution - To resolve the issue remove the ? in SAS token and Bingo...issue resolved

i,e use sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2022-12-28T18:26:28Z&st=2022-12-15T10:26:28Z&spr=https&sig=PLZSqBdl%2BHAu5eu0xMSKsy8sc3cQ%2FRKVh3JAX3y8amQ%3D

instead of 

?sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2022-12-28T18:26:28Z&st=2022-12-15T10:26:28Z&spr=https&sig=PLZSqBdl%2BHAu5eu0xMSKsy8sc3cQ%2FRKVh3JAX3y8amQ%3D

Azure Storage Gen 2 error - You do not have permissions to list the data using your user account with Azure AD. Click to learn more about authenticating with Azure AD.

 Issue - You do not have permissions to list the data using your user account with Azure AD. Click to learn more about authenticating with Azure AD.

Resolution

If you are using AAD Token, this error is telling you that you need to add a role assignment to the user. Please go to Storage account -> Access Control -> Add -> Add role assignment, then add Storage Blob Data Owner to your login account.

Add the user name under the Storage Blob Data Owner

SSIS - The package file is missing from the project directory

 Hi friends,

I got the above error when system restarted and upon reopening of visual studio ssis package ..my saved package was not found. 

To resolve this issue 

1. Open the file location by right clicking on project solution

2. Right click on package and click on Add package and choose your missing package.

This error occurs if the name of the SSIS package is recently changed

Bingo! Issue fixed

SSIS - conditional split output evaluated to NULL and it should return Boolean results

 Hi All,

Encountered above error when conditional split was used to implement SCD type 2 in SSIS using Look up

To resolve this use below code 

(ISNULL(email) ? "1" : email) != (ISNULL(L_email) ? "1" : L_email) || (ISNULL(phone) ? "1" : phone) != (ISNULL(L_phone) ? "1" : L_phone)

i,e use ISNULL for both source and destination side so that the query does not return any Null values and finally is able to evaluate results as Boolean

SSIS - Exception deserializing the package

 Hi Folks!

We get this error often with latest version of SSIS. To resolve this issue, follow below steps 

1. Go to Task Manager
2. Details Tab.
3. Locate the process “DtsDebugHost.exe“.
4. Kill this process. There might be multiple instances of this process. Kill all of them.
5. Reexecute SSIS package

Ah ha...issue resolved