cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KoenVdB Frequent Visitor
Frequent Visitor

Dataset refresh with API in Data Factory with Azure Function c#

Hi,

We have a solution implemented that over night our data processed into a Azure SQL Database using Azure Data Factory.

On that database a Power BI Dataset is created. We want to add an extra step in the Azure Data Factory Pipeline that will refresh the Dataset that is published and wait till the refresh is done. This will allow us to scale down the database back after the refresh is done.

 

I created a Azure function http trigger in C# that alows us to refresh the dataset. 

The datasetGroupId and DatasetId are passed to the function with the body of the POST request.

  public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)
        {

            var _RefreshStatus = "";
            var _StatusCode = HttpStatusCode.OK;
            var _AuthorityURI = Environment.GetEnvironmentVariable("PBI_AuthorityURI");
            var _ResourceURI = Environment.GetEnvironmentVariable("PBI_ResourceURI");
            var _APIURI = Environment.GetEnvironmentVariable("PBI_APIURI");
            var _DatasetRefreshURI = Environment.GetEnvironmentVariable("PBI_DatasetRefreshURI");
            var _RefreshUser = Environment.GetEnvironmentVariable("PBI_RefreshUser");
            var _RefreshPassword = Environment.GetEnvironmentVariable("PBI_RefreshPassword");
            var _ClientId = Environment.GetEnvironmentVariable("PBI_ClientId");

            TokenCache _TC = new TokenCache();
            var _Credential = new UserPasswordCredential(_RefreshUser, _RefreshPassword);
            var _AuthenticationContext = new AuthenticationContext(_AuthorityURI, _TC);
            var _AuthenticationResult = _AuthenticationContext.AcquireTokenAsync(_ResourceURI, _ClientId, _Credential).Result;

            // Get the requested data from the body 
            dynamic data = await req.Content.ReadAsAsync<object>();
            string _DatasetName = data?.DatasetName;
            string _DatasetGroupId = data?.DatasetGroupId;
            string _DatasetId = data?.DatasetId;

            try
            {
                HttpWebRequest _Request;
                _Request = System.Net.HttpWebRequest.CreateHttp(string.Format(_DatasetRefreshURI, _DatasetGroupId, _DatasetId));

                _Request.KeepAlive = true;
                _Request.Method = "POST";
                _Request.ContentLength = 0;
                _Request.ContentType = "application/json";

                _Request.Headers.Add("Authorization", _AuthenticationResult.CreateAuthorizationHeader());

                using (Stream writer = _Request.GetRequestStream())
                {
                    var _Response = (HttpWebResponse)_Request.GetResponse();
                    _RefreshStatus = _Response.StatusDescription.ToString();
                    _StatusCode = _Response.StatusCode;
                }

            }
            catch (WebException wex)
            {
                _StatusCode = HttpStatusCode.BadRequest;

            }
            catch (Exception ex)
            {
                _StatusCode = HttpStatusCode.BadRequest;
            }

            return req.CreateResponse(_StatusCode, "Refresh databset: " + _DatasetName + " - Status: " + _StatusCode.ToString());



        }

When we run the function in the emulator and using postman to send the POST request the refresh is triggered. Now when i publish the Azure Function to Azure and i call the specific function in Data Factory the following error appears:

 

 

Error_ADF.png

 

Does anybody has an idea how to fix this issue?

And also the issue that we want to wait until the refresh is done before exiting the function?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Dataset refresh with API in Data Factory with Azure Function c#

@KoenVdB ,

 

When integrating with Azure services, you may have errors raised that originate from the APIs of the underlying services. Links to the error code documentation for these services can be found in the Exceptions and return codes section of the following trigger and binding reference topics:

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Dataset refresh with API in Data Factory with Azure Function c#

@KoenVdB ,

 

When integrating with Azure services, you may have errors raised that originate from the APIs of the underlying services. Links to the error code documentation for these services can be found in the Exceptions and return codes section of the following trigger and binding reference topics:

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team
Community Support Team

Re: Dataset refresh with API in Data Factory with Azure Function c#

@KoenVdB ,

 

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

 

Regards,

Jimmy Tao

KoenVdB Frequent Visitor
Frequent Visitor

Re: Dataset refresh with API in Data Factory with Azure Function c#

Hi,

 

This is still not fixed. I'm a little bit closer to the solution but still having issues, after refacturing a lot of things.

When i run the code in a console app, it all works fine, but in an azure function i receive always this error message:

"Attempted to access an element as a type incompatible with the array. - at System.Collections.Generic.List`1.Add(T item)\r\n at Microsoft.PowerBI.Api.V2.PowerBIClient.Initialize()\r\n at Microsoft.PowerBI.Api.V2.PowerBIClient..ctor(Uri baseUri, ServiceClientCredentials credentials, DelegatingHandler[] handlers)\r\n 

 

kind regards

Koen