Querying Azure SQL Database using Azure Functions 2.0 to return JSON data

The guide below shows how you can easily query Azure SQL Database using Azure Functions.

I have to admit, I have to do multiple google search and combine it for a working solution.


  1. How to get SQL connectionString from Azure Function settings. https://docs.microsoft.com/en-us/azure/azure-functions/functions-scenario-database-table-cleanup
  2. How to convert the sql results to JSON.  https://stackoverflow.com/questions/5083709/convert-from-sqldatareader-to-json


    • 1. Create a new Function with HTTP trigger
    • 2. Add a new file called serialize.csx with following contents below. This will convert the SQL rows to a JSON like data.
using System.Text;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
public static IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
var results = new List<Dictionary<string, object>>();
var cols = new List<string>();
for (var i = 0; i < reader.FieldCount; i++)
var colName = reader.GetName(i);
var camelCaseName = Char.ToLowerInvariant(colName[0]) + colName.Substring(1);

while (reader.Read())
results.Add(SerializeRow(cols, reader));

return results;
private static Dictionary<string, object> SerializeRow(IEnumerable<string> cols,
SqlDataReader reader) {
var result = new Dictionary<string, object>();
foreach (var col in cols)
result.Add(col, reader[col]);
return result;


3. In the run.csx, paste the following code. This will query the Azure SQL database and returns the data.

#r "Newtonsoft.Json"
#load "serialize.csx"

using System.Net;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Extensions.Primitives;

using Newtonsoft.Json;

using System.Text;

using System.Data;

using System.Linq;

using System.Configuration;

using System.Data.SqlClient;

using System.Collections.Generic;

public static async Task<HttpResponseMessage> Run(HttpRequest req, ILogger log)


log.LogInformation("C# HTTP trigger function processed a request.");

string name = req.Query["name"];

string json =" ";



var str = Environment.GetEnvironmentVariable("<ConnectionStringName in appSettings>");

using(SqlConnection conn =new SqlConnection(str))


using(SqlCommand cmd =new SqlCommand())


SqlDataReader dataReader;

cmd.CommandText = "<SQL QUERY HERE>";

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;


dataReader = cmd.ExecuteReader();

var r = Serialize(dataReader);

json = JsonConvert.SerializeObject(r, Formatting.Indented);




catch(SqlException sqlex)




returnnew HttpResponseMessage(HttpStatusCode.BadRequest)


Content = new StringContent(JsonConvert.SerializeObject($"The following SqlException happened: {sqlex.Message}"), Encoding.UTF8, "application/json")



catch(Exception ex)




returnnew HttpResponseMessage(HttpStatusCode.BadRequest)


Content = new StringContent(JsonConvert.SerializeObject($"The following SqlException happened: {ex.Message}"), Encoding.UTF8, "application/json")



returnnew HttpResponseMessage(HttpStatusCode.OK)


Content = new StringContent(json, Encoding.UTF8, "application/json")


This shows how you can easily query the Azure SQL Database and return the data JSON in few minutes.
Perhaps next steps is to deploy this Azure Function into API management.


BizTalk – BTDF Compilation Error – ICE01

Current setup:

  • BizTalk Server 2016
  • Azure DevOps On-premise Build Agent

Error Message:

Error executing ICE action ‘ICE01’. The most common cause of this kind of ICE failure is an incorrectly registered scripting engine. See http://wix.sourceforge.net/faq.html#Error217 for details and how to solve this problem. The following string format was not expected by the external UI message logger: “The Windows Installer Service could not be accessed. This can occur if the Windows Installer is not correctly installed. Contact your support personnel for assistance


Change the Log On As of VSTS Agent to Local System Account

  1. Run services.msc
  2. Look for the VSTS Agent Service
  3. On Tab change the Log On from Network Service to Local System account


SAP – How to generate an XSD/IDoc Schema

Below are the steps on how to generate an XSD Schema / IDoc Schema from SAP.

1. In the SAP System in which the IDoc is defined, call transaction WE60 (IDoc Documentation).

2. Enter the name of the IDoc in the Basic type field (or the Enhancement field if your IDoc is an Enhancement of a standard SAP IDoc).

3. Choose menu Documentation, XML Schema.

4. Respond Yes, if prompted to ‘Generate Documentation for Unicode File’.

5. On the resulting screen containing the XSD of the IDoc, choose menu XML, Download.

BizTalk – SFTP Adapter – Missing WinSCPNet Library


This guide is applicable to BizTalk Server 2016 with Feature Pack 3 Installed.

Version of WinSCPNet library is different once CU5/FP3 have been installed.

Original BizTalk Server 2016 installation requires 5.7.7.

More info here:

BizTalk Server 2016 Prerequisites:


CU5/FP3 Info:



The Messaging Engine failed to add a receive location “FileSync.SFTP” with URL “sftp://raclede.net/files/*.*” to the adapter “SFTP”. Reason: “System.IO.FileLoadException: Could not load file or assembly ‘WinSCPnet, Version=, Culture=neutral, PublicKeyToken=2271ec4a3c56d0bf’ or one of its dependencies. General Exception (Exception from HRESULT: 0x80131500)

How to solve:

  1. Copy the both WinSCP exe and dll (5.13.1) to BizTalk Installation Path (Do not GAC the dll)
    1. WinSCP.exe
    2. WinSCPnet.dll

How to get the exe and dll

  1. Use Nuget in Visual Studio to get the specific version of WinSCP.

Nuget-WinSCP    2.  Once added, just browse to the lib for the dll and tools folder for the .exe





All receive location green and by the moment you’ve send your first IDoc this warning occurs:

The adapter “WCF-Custom” raised an error message. Details “Microsoft.Adapters.SAP.RFCException: Details: ErrorCode=RFC_INVALID_HANDLE. AdapterErrorMessage=An exception has occurred on the listener while executing RfcWaitForRequest..”.

To solve this error you’ll need to install the BizTalk SAP Adapter Schema.


  1. Create a BizTalk application or use the default BizTalk Application
  2. Right Click Add Resource.
  3. Browse to: <install path of Microsoft BizTalk Adapter Pack(x64)>\bin\Microsoft.Adapters.SAP.BiztalkPropertySchema.dll
  4. In the options check all.


BizTalk 2016 – Installing SAP Adapter- Microsoft.Adapters.SAP.SAPGInvoker.dll

After installing BizTalk adapter pack you would expect that enabling the SAP receive location port will just work. Guess what? Error!

Could not load file or assembly ‘Microsoft.Adapters.SAP.SAPGInvoker.dll’ or one of its dependencies. The specified module could not be found.
File name: ‘Microsoft.Adapters.SAP.SAPGInvoker.dll’

To solve this error you’ll need to install the SAP Libraries. Since i have a copy of previous SAP libraries, I’ve just reinstalled those, otherwise you’ll have to download it from SAP Market Place (you’ll need someone from your company that has access to those).


  1. Install SAP NCO Libraries:
    1. NCo3018_Net40_x64.msi
    2. NCo3018_Net20_x86.msi
  2. Copy the SAP 3.0 libraries both x86 = 32bit, x64 = 64bit on the correct folder
    1. icudt26l.dll
    2. icuin26.dll
    3. icuuc26.dll
    4. librfc32u.dll
    5. libsapu16vc71.dll
    6. libsapucum.dll

32bit folder = C:\Windows\SysWOW64

64bit folder = C:\Windows\System32

3.  Restart the host instance


SSIS 2012 to SSIS 2016 Migration – Arithmetic operation resulted in an overflow

We’ve recently upgraded our SSIS 2012 to SSIS 2016, during the package upgrade it goes without issue. In Visual Studio, there’s no problem running it, however when we use the SSIS runtime we’ve encountered the following error:

“Arithmetic operation resulted in an overflow.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

The Execute method on the task returned error code 0x80131516 (Arithmetic operation resulted in an overflow.). The Execute method must succeed, and indicate the result using an “out” parameter.

To solve this issue,  I’ve recreated the SQL Task and viola no error!.