SSIS migration into Azure managed Instance

Question

Hi Team,

We are running SSIS packages on premise SQL server and planning to migrate into Azure SQL Manages instance.
We are using 7z.exe and winSCP to zip.csv files and uploading them via the SFTP path and will schedule the SSIS package via the SQL Server agent in Azure SQL MI. Is it possible to achieve this on Azure SQL MI. Please help on this.

Answer 1

Hi @Thyagarajulu B M ,

You may also refer to how-to-migrate-ssis-packages-managed-instance.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

It is possible.

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;  
}  

Just make sure you add any required DLLs to Azure-SSIS IR using this guide.