Configuring Azure Blob Destination
Configuring Azure Blob Destination
Open visual studio 2015 (VS 2013 will work too) and create a new Integration Service project. Make sure you have SQL Server Data Tools and Azure Feature Pack for Integration Services (SSIS) installed.
Drag a data flow task to control flow. Double click the data flow task to open the data flow task tab. On the Data Flow task tab, configure SQL Server source or any other source of you would like to move data from. I will upload Application.People table from WideWorldImporters database. You can download the WideWorldImporters database from here.
Once you are done configuring the source, drag and drop the Azure Blob Destination from the Azure section in SSIS toolbox. Double click the Azure Blob Destination to configure it.
Configuring Azure Storage Connection Manager
In Azure Blob Destination Editor window, first thing we’ll do is to configure the connection manager. Select New besides the Azure Storage Connection Manager.
In the Azure Storage Connection Manager Editor, check Use Azure Storage option and provide the Storage Account Name and Account Key.
Select Test Connection to make sure you connect successfully to the Azure Storage account. Click OK to continue.
In the location section of Azure Blob Destination Editor, provide the container name and the blob name.
In the Format section of the Azure Blob Destination Editor, provide the blob file format (text/Avro) and select the column delimiter character.
In the Compression section of the Azure Blob Destination Editor, check the compress a file option, and select the compression type and level you want.
Click ok to continue. Save and run the package. The package runs successfully and 1111 rows are uploaded to the blob storage.
To verify, you can either navigate to the Azure Storage container on the Azure Portal or Azure Storage explorer.
Below is a snapshot from Azure portal conforming that the table was uploaded as a blob on Azure Storage.
The Azure Blob Destination component only allows us to upload a data from heterogenous data sources supported by SSIS to Azure Storage.
Open visual studio 2015 (VS 2013 will work too) and create a new Integration Service project. Make sure you have SQL Server Data Tools and Azure Feature Pack for Integration Services (SSIS) installed.
Drag a data flow task to control flow. Double click the data flow task to open the data flow task tab. On the Data Flow task tab, configure SQL Server source or any other source of you would like to move data from. I will upload Application.People table from WideWorldImporters database. You can download the WideWorldImporters database from here.
Once you are done configuring the source, drag and drop the Azure Blob Destination from the Azure section in SSIS toolbox. Double click the Azure Blob Destination to configure it.
Configuring Azure Storage Connection Manager
In Azure Blob Destination Editor window, first thing we’ll do is to configure the connection manager. Select New besides the Azure Storage Connection Manager.
In the Azure Storage Connection Manager Editor, check Use Azure Storage option and provide the Storage Account Name and Account Key.
Select Test Connection to make sure you connect successfully to the Azure Storage account. Click OK to continue.
In the location section of Azure Blob Destination Editor, provide the container name and the blob name.
In the Format section of the Azure Blob Destination Editor, provide the blob file format (text/Avro) and select the column delimiter character.
In the Compression section of the Azure Blob Destination Editor, check the compress a file option, and select the compression type and level you want.
Click ok to continue. Save and run the package. The package runs successfully and 1111 rows are uploaded to the blob storage.
To verify, you can either navigate to the Azure Storage container on the Azure Portal or Azure Storage explorer.
Below is a snapshot from Azure portal conforming that the table was uploaded as a blob on Azure Storage.
The Azure Blob Destination component only allows us to upload a data from heterogenous data sources supported by SSIS to Azure Storage.
Configuring Azure Blob Upload Task
Azure Blob Upload Task can be used to upload one more files to azure blob storage. To explain how this works, I’ll demonstrate how to upload all table data from WideWorldImporters database to Azure blob storage.
The first thing is to script out all table data from WideWorldImporters database. I’ll do this through below PowerShell script.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Param(
[Parameter(Mandatory=$True)]
[string]$server,
[Parameter(Mandatory=$True)]
[string]$database,
[Parameter(Mandatory=$True)]
[string]$exportfolder
)
# get all tables in a database
$qrygetallusertables = "SELECT '[' + sys.schemas.name + '].[' + sys.tables.name + ']' AS tablename FROM sys.tables JOIN sys.schemas ON schemas.schema_id = tables.schema_id WHERE type = 'U';"
# execute the query
$result = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $qrygetallusertables
foreach($row in $result)
{
$table = $row.Item("tablename")
$qrygetdata = "SELECT * FROM $table";
$table = $table.Replace("[","")
$table = $table.Replace("]","")
$csvpath = $exportfolder + "\" + $table + ".csv"
$zipfilepath = $exportfolder + "\" + $table + ".zip"
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $qrygetdata | Export-Csv $csvpath -Force
Compress-Archive -LiteralPath $csvpath -DestinationPath $zipfilepath -CompressionLevel Optimal
}
The above powershell script takes Server, Database and path to the folder where the table data will be exported as CSV.
You can either run the script separately to generate the CSVs or you can use Execute Process Task to call the PowerShell script from SSIS.
Create a new Integration Service project and drag and drop an Execute Process Task on the control flow. Configure it as shown below.
In the Process pane, enter the path of PowerShell.exe under the Executable section.
“C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe”
Select Expressions tab and enter the following expression for the Arguments section.
“-File C:\\Projects\\70475\\Scripts\\ExportAllTableDataToCSV.ps1 -server ” + @[User::server] + ” -database ” + @[User::database] + ” -exportfolder ” + @[User::localdirectory]
You will have to add Server, Database and exportfolder variables in SSIS package. These variables correspond to the parameters required to run the PowerShell script discussed above.
The Execute Process Task will run the powershell script which will export all table data from WideWorldImporters database to a folder.
Click Ok to save the configuration for Execute Process Task.
Drag and drop Azure Blob Upload Task to the control flow. Connect Execute Process Task to Azure Blob Upload Task. Double click the Azure Blob Upload Task and configure it as shown below.
Provide the AzureStorageConnection name. Check the previous steps on how to configure a new Azure Storage Connection.
Provide the BlobContainer and the BlobDirectory. This is where the files will be uploaded on Azure Storage.
Provide the LocalDirectory which has the files to be uploaded to the Azure Storage.
You can also provide file level filters. I only want to uploade *.zip files to the Azure Storage so I have FileName filter as *.zip
Easy and simple. Save and run the package. The Execute Process Task will run the PowerShell script with the required parameters. The PowerShell script, exports table data in csv file and then zips the file.
The Azure Blob Upload Task will then upload all the files from the export folder to Azure blob.
You can verify the files either from the Azure Portal or Azure Storage Explorer.
Azure Blob Upload Task can be used to upload one more files to azure blob storage. To explain how this works, I’ll demonstrate how to upload all table data from WideWorldImporters database to Azure blob storage.
The first thing is to script out all table data from WideWorldImporters database. I’ll do this through below PowerShell script.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
Param(
[Parameter(Mandatory=$True)]
[string]$server,
[Parameter(Mandatory=$True)]
[string]$database,
[Parameter(Mandatory=$True)]
[string]$exportfolder
)
# get all tables in a database
$qrygetallusertables = "SELECT '[' + sys.schemas.name + '].[' + sys.tables.name + ']' AS tablename FROM sys.tables JOIN sys.schemas ON schemas.schema_id = tables.schema_id WHERE type = 'U';"
# execute the query
$result = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $qrygetallusertables
foreach($row in $result)
{
$table = $row.Item("tablename")
$qrygetdata = "SELECT * FROM $table";
$table = $table.Replace("[","")
$table = $table.Replace("]","")
$csvpath = $exportfolder + "\" + $table + ".csv"
$zipfilepath = $exportfolder + "\" + $table + ".zip"
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $qrygetdata | Export-Csv $csvpath -Force
Compress-Archive -LiteralPath $csvpath -DestinationPath $zipfilepath -CompressionLevel Optimal
}
|
The above powershell script takes Server, Database and path to the folder where the table data will be exported as CSV.
You can either run the script separately to generate the CSVs or you can use Execute Process Task to call the PowerShell script from SSIS.
Create a new Integration Service project and drag and drop an Execute Process Task on the control flow. Configure it as shown below.
In the Process pane, enter the path of PowerShell.exe under the Executable section.
“C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe”
Select Expressions tab and enter the following expression for the Arguments section.
“-File C:\\Projects\\70475\\Scripts\\ExportAllTableDataToCSV.ps1 -server ” + @[User::server] + ” -database ” + @[User::database] + ” -exportfolder ” + @[User::localdirectory]
You will have to add Server, Database and exportfolder variables in SSIS package. These variables correspond to the parameters required to run the PowerShell script discussed above.
The Execute Process Task will run the powershell script which will export all table data from WideWorldImporters database to a folder.
Click Ok to save the configuration for Execute Process Task.
Drag and drop Azure Blob Upload Task to the control flow. Connect Execute Process Task to Azure Blob Upload Task. Double click the Azure Blob Upload Task and configure it as shown below.
Provide the AzureStorageConnection name. Check the previous steps on how to configure a new Azure Storage Connection.
Provide the BlobContainer and the BlobDirectory. This is where the files will be uploaded on Azure Storage.
Provide the LocalDirectory which has the files to be uploaded to the Azure Storage.
You can also provide file level filters. I only want to uploade *.zip files to the Azure Storage so I have FileName filter as *.zip
Easy and simple. Save and run the package. The Execute Process Task will run the PowerShell script with the required parameters. The PowerShell script, exports table data in csv file and then zips the file.
The Azure Blob Upload Task will then upload all the files from the export folder to Azure blob.
You can verify the files either from the Azure Portal or Azure Storage Explorer.
No comments:
Post a Comment