Script task to upload zip file to blob storage with azure datafactory SSIS


  • I have a azure data factory project. I need to query some data from my Azure SQL Database then load into an xml, zip it and upload to blob sotrage. I don't want to write anything to the file system (because I think the Azure Database doesn't have any lcoal storage) so I am using the Memorystream.

    This Script Task is working on my local SSIS database but not on the Azure Datafactory:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Collections;
    using System.Linq;
    using System.Data.OleDb;
    using System.IO;
    using System.IO.Compression;
    using System.Data.SqlClient;
    using Microsoft.Azure;
    using Microsoft.WindowsAzure.Storage;
    using Microsoft.WindowsAzure.Storage.Auth;
    using Microsoft.WindowsAzure.Storage.Blob;
    public void Main()
                CloudStorageAccount storageAccount = null;
                CloudBlobContainer cloudBlobContainer = null;
                    DataSet ds = new DataSet("FullList");
                    OleDbDataAdapter oleDa = new OleDbDataAdapter();
                    DataTable dt = new DataTable("CustomerTable");
                    oleDa.Fill(dt, Dts.Variables["User::CustomerSelect"].Value);
                    DataTable dt_product = new DataTable("ProductTable");
                    oleDa.Fill(dt_product, Dts.Variables["User::ProductSelect"].Value);
                    DataRelation relation = ds.Relations.Add("relation", ds.Tables["CustomerTable"].Columns["id"], ds.Tables["ProductTable"].Columns["id"]);
                    relation.Nested = true;
                    string connstring = Dts.Connections["testolgdev"].AcquireConnection(Dts.Transaction).ToString();
                    if (CloudStorageAccount.TryParse(connstring, out storageAccount))
                            CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
                            cloudBlobContainer = cloudBlobClient.GetContainerReference("flat");
                            string fileName = "xml" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".zip";
                            var blob = cloudBlobContainer.GetBlockBlobReference(fileName);
                            using (var stream = new ZipArchive(blob.OpenWrite(), ZipArchiveMode.Create))
                                var entry = stream.CreateEntry("test_dataset_fullresult_onlymem.xml");
                                using (var es = entry.Open())
                        catch (StorageException ex)
                            Console.WriteLine("Error returned from the service: {0}", ex.Message);
                        Console.WriteLine("Wrong connection string");
                catch (TargetInvocationException e)
       Dts.TaskResult = (int)ScriptResults.Success;

    This is the Azure Datafactory SSIS error when I deploy and execute it:

    Script Task 1:Error: Could not load file or assembly 'Microsoft.WindowsAzure.Storage, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

    Is it possible to fix this? Can I add the missing dll to Azure Datafactory?

    Monday, May 7, 2018 7:35 AM


All replies