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

    Question

  • 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;
    
    
                try
                {
    
                    DataSet ds = new DataSet("FullList");
                    OleDbDataAdapter oleDa = new OleDbDataAdapter();
    
                    DataTable dt = new DataTable("CustomerTable");
                    oleDa.Fill(dt, Dts.Variables["User::CustomerSelect"].Value);
                    ds.Tables.Add(dt);
    
                    DataTable dt_product = new DataTable("ProductTable");
                    oleDa.Fill(dt_product, Dts.Variables["User::ProductSelect"].Value);
                    ds.Tables.Add(dt_product);
    
    
    
                    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))
                    {
                        try
                        {
                            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())
                                {
                                    ds.WriteXml(es);
                                }
    
                                
                            }
    
    
    
                        }
                        catch (StorageException ex)
                        {
                            Console.WriteLine("Error returned from the service: {0}", ex.Message);
                        }
                    }
                    else
                    {
                        Console.WriteLine("Wrong connection string");
                    }
    
    
    
                }
                catch (TargetInvocationException e)
                {
     
                    throw;
                }
    			
       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=4.3.0.0, 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

Answers

All replies