BCP in Azure Functions

  • Article

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