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

Power BI - Month is not getting sorted even when Sort by Month Number is applied

 Hi Guys,

My team encountered the below error 

Power BI - Month is not getting sorted even when Sort by Month Number is applied

Resolution - Check the data type for Month Number. In our case it was of "Text" type. When we changed to "Whole Number" it worked 

Bingo!

Power BI - the column " " of the table wasn't found..while importing data from Azure Data Lake

 Hi Folks,

I got the below error while trying to import data fro Azure BLOB

the column " " of the table wasn't found

Resolution - Check if there are any "Removed columns" steps in your Power Query steps. If yes, try to remove it and reload the data. It will work

2nd step - Try to refresh the table which you are trying to load