Power BI - Could not load file or assembly System.EnterpriseServices

Scenario: when i open a power bi dashboard (.pbix) file, i get an error "Could not load file or assembly 'System.EnterpriseServices', Version=4.0.0.0)

Solution: Start your Power BI Desktop by running it as administrator. Now you will either pass through the error and your dashboard will open or you will see the actual error (most common: database login failure)

SQL: Violation of primary key constraint found duplicate key value

Scenario: we are inserting a value in our table that has a primary key constraint enabled. While inserting, we get an error: "Violation of primary key constraint 'xyz'. Cannot insert duplicate key in object 'dbo.customertbl'. The duplicate key value is (4).

Resolution: The error says that the value you are inserting in your primary key enabled column is already present in the column. Select your table and search for the value listed in your error "duplicate key value is (value)". 

You have two options:

• Delete the value from the table (not recommended)
• Check why the duplicate value is coming from the source query that you are inserting into this table?.
•  Most likely, you might have to either alter your primary key and make it a composite key (multiple columns) or see maybe the data fetching query is not functioning properly (fix the query)

Azure Data Factory SqlErrorNumber 8672: A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times

Scenario: We have a copy activity in the data factory pipeline that, copies data from Azure Blob Storage (ADLS) to Azure SQL. in the sink, we are using SQL stored procedure to upsert the data using Merge statement. In the stored procedure, we are passing the Table type that holds the data from source.

While running the pipeline, it fails giving the below error message:

• Error: The MERGE statement attempted to UPDATE or DELETE the same row more than once.
• { "errorCode": "2200", "message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.,Source=.Net SqlClient Data Provider,SqlErrorNumber=8672,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=8672,State=1,Message=The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.,},],'", "failureType": "UserError", "target": "Copy data to Azure SQL", "details": [] }

Solution: On Analyzing, we found that in our SQL table, we had one column set to not null that we are no longer using in our database. after deleting this column from the SQL table, our pipeline ran successfully. 

• If we require this column, then we would have checked if the source is sending the data in this column or not. if the data was not regular (we see some nulls), then we would have edited the schema of the table to make this column allow Nulls.

Azure data factory - datetime error in copy activity when loading data in SQL database

Today we will learn on how to deal with loading datetime fields in the database using Data Factory

Scenario: Suppose you have a datetime field in source that you are trying to load into database through data factory v2. you sometime gets an error that datetime is converted into datetime2 or datetime misses loading the datetime column into database. follow the below steps:

Steps:

• In the copy activity, change the Mapping of the source to string.

• Use stored procedure approach to load the data into SQL database
• In the Sink section of copy activity, specify the stored procedure name, table type, and table type parameter

• In the stored procedure, convert string into datetime
• Link to stored procedure approach: https://www.datastackpros.com/2020/03/azure-data-factory-upsert-using.html
• Run the pipeline

Alternate solution:

•  Try changing the Mapping of the source to Datetime
•  specify the date format "yyyy-MM-dd"

• Run the pipeline
• it will convert the date into the specified format

Azure SQL - Script to create logins and give appropriate permissions to the user / login

Today we will learn on how to create a script that creates logins and give appropriate permissions within Azure SQL

Run the below script in your Azure SQL server:

--create a login. supply the login account name
-- login can also be a user's azure login email that you want to add to SQL to give permissions

use [master]
create login [<login account>] from external provider
go

----add the login to the database.
use [<Database name>]
go
create user [<login account>] from login [<login account>] with default_schema = [DBO];
go

---Create a db_executor role within the database
use [<Database name>]
go
create role [db_executor]
go
Grant execute on schema ::dbo to db_Executor
go


---give db executor permission to the user for the database
exec sp_addrolemember N'db_executor',N'<login account>'
go

---give data reader permission to the user for the database
exec sp_addrolemember N'db_datareader',N'<login account>'
go

---give data writter permission to the user for the database
exec sp_addrolemember N'db_datawriter',N'<login account>'
go

---give ddl admin permission to the user for the database
exec sp_addrolemember N'db_ddladmin',N'<login account>'
go

Azure Data Factory: Upsert using Data Flows

Today we will learn on how to perform upsert in Azure data factory (ADF) using data flows

Scenario:

• We will be ingesting a csv stored in Azure Storage (ADLS V2) into Azure SQL by using Upsert method

Steps:

1. Create two connections (linked Services) in the ADF:

       1. One for the csv stored in ADLS

       2. One for the target Azure SQL

                                                 

Informatica Cloud - Ingest Data from Rest Api into Azure SQL

Today we will learn on how to fetch data from Rest Api and ingest into Azure SQL using Informatica Cloud data integration

Steps to Ingest data from REST API to Azure SQL DB via Informatica:

1.    Create a test SQL Server Database in Azure

·        Setup a new server for the database

·        For testing, use “Public endpoint” while setting up Azure server for database (this has to be properly setup when using for production)

·        Add your current computer’s IP address in the Azure server firewall

·        Add Informatica server’s IP address in the Azure server firewall

o   If you don’t add informatica server’s IP, Informatica will throw error “client with IP *** is not allowed to access the server”

·        Click Create the server.

·        Once the server is created, click OK to create the database