locked
Copy Azure Blob Excel to Azure SQL using ADF RRS feed

  • Question

  • Hello Everyone,

    My requirement  is 

    (1) I am getting Excel files with multiple tabs  in Local folder/FTP 

    (2) first I need to populate this data in Azure SQL Stage environment

    (3) Once Data will be available in stage, I need to apply various transformation and need to create final presentation layer from which Power BI reports will be generated.



    • Edited by Aminesh Saturday, February 23, 2019 5:26 PM
    Tuesday, February 19, 2019 5:33 AM

Answers

  • Hi Aminesh,

    Yes, you can provision the Azure-SSIS Integration Runtime in Azure Data Factory.

    This tutorial provides steps for using the Azure portal to provision an Azure-SSIS integration runtime (IR) in Azure Data Factory. Then, you can use SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS) to deploy and run SQL Server Integration Services (SSIS) packages in this runtime in Azure. For conceptual information on Azure-SSIS IRs, see Azure-SSIS integration runtime overview.

    Hope this helps.

    Tuesday, February 26, 2019 10:46 AM

All replies

  • Hi Aminesh,

    If you are able to save the Excel data as a CSV file, you could provide that as the source either on premises or via a blob in Azure storage. It is then a simple copy activity to move the data.

    For more details, refer the below links:

    Move data by using Copy Activity

    Copy data to and from an on-premises file system by using Azure Data Factory

    Copy data to or from Azure Blob Storage using Azure Data Factory

    Copy data from Azure Blob storage to a SQL database by using the Copy Data tool

    Step by step to Create a pipeline to load data from a csv sample data file to an Azure SQL database

    OR

    You can use the bcp command-line utility to import data from a CSV file into Azure SQL Database. For more details, refer Load data from CSV into Azure SQL Database (flat files).

    OR

    You can use Azure flow or Logic Apps for this right now using Excel connector.

    https://docs.microsoft.com/en-us/connectors/excel/

    Hope this helps.

    Tuesday, February 19, 2019 8:15 AM
  • Hi Aminesh,

    Just checking in to see if the above answer helped. If this answers your query, do click “Mark as Answer” and Up-Vote for the same. And, if you have any further query do let us know.

    Thursday, February 21, 2019 9:35 AM
  • Hi,

    Do I need to fist copy files from Local folder to Azure Blob or I can directly read Local file and insert data into SQL azure.

    Also, in case of option 1 to move file from Local folder to Blob, I can Use SSIS ( Azure upload task) or you are suggesting some other alternative.

    Friday, February 22, 2019 7:18 AM
  • Hi Aminesh,

    Option1: Create destination table in Azure SQL database and use BCP command-line utility to import data from a csv file to Azure SQL Database.

    Option2: Using Azure Data Factory

    You need to convert Excel files to CSV files and copy to Azure Blob Storage using any tool like (AzCopy, Storage Explorer, Azure Portal), then once the data is available on Azure Blob Storage, you can use ADF copy activity to move to Azure SQL database.

    Hope this helps.

    Friday, February 22, 2019 7:56 AM
  • Hi,

    I am really confuse about actual implementation.

    As I said earlier ,My requirement  is 

    (1) I am getting Excel files with multiple tabs  in Local folder/FTP 

    (2) first I need to populate this data in Azure SQL Stage environment

    (3) Once Data will be available in stage, I need to apply various transformation and need to create final presentation layer from which Power BI reports will be generated.

    My Plan ( Please correct me as I am from SSIS background)

    As ADF doesn't support directly read data/copy data from Excel

    (1) I will create SSIS Package which reads data from mutiple tabs of Excel and Copy data in Azure SQL- stage area and run package in ADF using SSIS Integration Runtime

    (2) To apply various transformation, I can use Data flow feature of V2 ( currently in preview) and perform various transformation activity.Here my source is Azure SQL Stage DB to Azure SQL Presentation DB

    or 

    I will create again SSIS PAckage which will do various transformation and run in ADF using SSIS integration runtime.

    Earlier I thought to copy files from On-Prem to Azure Blob but reading Excel data from Blbo is not possible.

    Please confirm above understanding or provide me best suitable option for my above requirement.

    Thanking you in advance.

    Saturday, February 23, 2019 5:18 PM
  • My requirement  is 

    (1) I am getting Excel files with multiple tabs  in Local folder/FTP 

    (2) first I need to populate this data in Azure SQL Stage environment

    (3) Once Data will be available in stage, I need to apply various transformation and need to create final presentation layer from which Power BI reports will be generated.

    My Plan ( Please correct me as I am from SSIS background)

    As ADF doesn't support directly read data/copy data from Excel

    (1) I will create SSIS Package which reads data from mutiple tabs of Excel and Copy data in Azure SQL- stage area and run package in ADF using SSIS Integration Runtime

    (2) To apply various transformation, I can use Data flow feature of V2 ( currently in preview) and perform various transformation activity.Here my source is Azure SQL Stage DB to Azure SQL Presentation DB

    or 

    I will create again SSIS PAckage which will do various transformation and run in ADF using SSIS integration runtime.

    Earlier I thought to copy files from On-Prem to Azure Blob but reading Excel data from Blbo is not possible.

    Please confirm above understanding or provide me best suitable option for my above requirement.

    Thanking you in advance.

    Monday, February 25, 2019 10:26 AM
  • Hi Aminesh,

    Sorry for the delayed response. Using SQL Server Integration Services, you can load data from multiple Excel Sheets and load it into SQL Server destination table.

    For more details, refer the links below.

    Import data from Excel to SQL Server or Azure SQL Database

    How to read data from multiple Excel worksheets with SQL Server Integration Services

    Once, the data is load into SQL Server destination table.  You can design a data transformation job in the data flow designer by constructing a series of transformations. Start with any number of source transformations followed by data transformation steps. Then, complete your data flow with sink to land your results in a destination.

    For more details, refer “Mapping Data Flow Datasets”.

    Hope this helps.

    Tuesday, February 26, 2019 9:17 AM
  • Hi ,

    Thanks for your reply.

    Still just wanted to confirm  in your above post you have mentioned :

    Using SQL Server Integration Services, you can load data from multiple Excel Sheets and load it into SQL Server destination table.

    So, I understand that I will create SSIS package .But, my confusion /worry area is that Can I run that package  in ADF via Integration runtime as ADF is completely new for me  and It is the technology client want to use us.

    It would be great you can confirm for the same.

    Tuesday, February 26, 2019 10:29 AM
  • Hi Aminesh,

    Yes, you can provision the Azure-SSIS Integration Runtime in Azure Data Factory.

    This tutorial provides steps for using the Azure portal to provision an Azure-SSIS integration runtime (IR) in Azure Data Factory. Then, you can use SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS) to deploy and run SQL Server Integration Services (SSIS) packages in this runtime in Azure. For conceptual information on Azure-SSIS IRs, see Azure-SSIS integration runtime overview.

    Hope this helps.

    Tuesday, February 26, 2019 10:46 AM