In this post, let us see how to load files from local folder into Azure SQL Data Warehouse using Azure SQL DW Upload Task in SSIS.
We need to download and install Azure Feature pack to get the Azure components in SSIS.
Azure SQL DW Upload Task is designed to work in such a way that it consumes the files in local folder and places in Azure Blob storage as staging (there is an option to retain files within Blob storage) and then it is loaded into Azure SQL data warehouse.
After creating an SSIS package, drag & drop Azure SQL DW Upload Task into Control Flow.
In the task editor, first let us provide source information - Local Directory, Recursively, FileName
Create connection to Azure Blob storage and provide Blob storage details:
Create connection to Azure SQL data warehouse
We also have an option to create destination table on the fly (while package is executed) by mentioning the table name as shown below:
By looking at above script, we can notice that Create table as Select mechanism is used to load the data into Azure SQL DW.
When I execute the package, it failed with below error message:
Created master key to resolve above error:
As shown in below image, after successful execution of package, from SSMS we can see Table, External Data Source, External File Format are created.
All setting of Azure SQL DW Task:
We need to download and install Azure Feature pack to get the Azure components in SSIS.
Azure SQL DW Upload Task is designed to work in such a way that it consumes the files in local folder and places in Azure Blob storage as staging (there is an option to retain files within Blob storage) and then it is loaded into Azure SQL data warehouse.
After creating an SSIS package, drag & drop Azure SQL DW Upload Task into Control Flow.
In the task editor, first let us provide source information - Local Directory, Recursively, FileName
Create connection to Azure Blob storage and provide Blob storage details:
Create connection to Azure SQL data warehouse
We also have an option to create destination table on the fly (while package is executed) by mentioning the table name as shown below:
In the task editor, T-SQL shows the script to load the data from Azure blob to Azure SQL data warehouse. We can also edit the script as shown below:
By looking at above script, we can notice that Create table as Select mechanism is used to load the data into Azure SQL DW.
When I execute the package, it failed with below error message:
Created master key to resolve above error:
As shown in below image, after successful execution of package, from SSMS we can see Table, External Data Source, External File Format are created.
All setting of Azure SQL DW Task:
No comments:
Post a Comment