BCP in Azure Functions


Here is sample code to execute BCP in Azure Functions

In this sample code, Azure Function will

  1. download a zip file from Azure Storage
  2. unzip this file using System.IO.Compression.ZipFile
  3. upload it to Azure SQL using System.Data.SqlClient.SqlBulkCopy

Here is the complete source code

Run.csx code is

#r "System.Data"
#r "System.IO.Compression.FileSystem"
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO; using System.IO.Compression;
using Microsoft.WindowsAzure.Storage;
public static void Run(TimerInfo myTimer, TraceWriter log) {
log.Info($"C# Timer trigger function executed at: {DateTime.Now}");

log.Info("Downloading zip file from storage....");
var storageAccount = CloudStorageAccount.Parse("DefaultEndpointsProtocol=https;AccountName=wabac;AccountKey===");
var blobClient = storageAccount.CreateCloudBlobClient();
var container = blobClient.GetContainerReference("bcp");
var blob = container.GetBlockBlobReference("Cars.zip");
blob.DownloadToFile($"{Path.GetTempPath()}\\cars.zip", FileMode.CreateNew);

log.Info("Downloaded zip file from storage");

log.Info("Try to unzip....");

var d = Directory.CreateDirectory($"{Path.GetTempPath()}\\unzipcars");

log.Info($"folder name is {d.FullName}");

Directory.Delete(d.FullName, true);
d = Directory.CreateDirectory($"{Path.GetTempPath()}\\unzipcars");
ZipFile.ExtractToDirectory($"{Path.GetTempPath()}\\cars.zip", d.FullName);

log.Info("unzip done");

var filename = d.GetFiles("*.csv")[0].FullName;

log.Info($"CSV filename is {filename}");

DataTable sourceData = new DataTable();

sourceData.Columns.Add("CarMake");
sourceData.Columns.Add("CarModel");
sourceData.Columns.Add("CarYear");

log.Info("Reading CSV file...");

using (var rd = new StreamReader(filename))
{
while (!rd.EndOfStream)
{
var splits = rd.ReadLine().Split(',');
sourceData.Rows.Add(splits[0], splits[1], splits[2]);
}
}

log.Info("BCP....");

SqlBulkCopy bcp = new SqlBulkCopy("Server=tcp:.database.windows.net,1433;Initial Catalog=;Persist Security Info=False;User ID=;Password=;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
bcp.DestinationTableName = "bcp";
bcp.WriteToServer(sourceData);

log.Info("upload to SQL done"); }

As we are using Azure Storage NuGet Package, we need to add the package name in the project.json file as shown below

{
"frameworks": {
"net46": {
"dependencies": {
"WindowsAzure.Storage": "7.2.1"

}
}
}
}

Complete source code is here


Comments (0)

Skip to main content