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?