|
SSIS Azure Storage Task (Blob Storage)
|
 
|
 |
Azure Blob Storage Task can be used to perform various operations with Azure Storage objects (blobs and containers) (e.g. Download, Upload, Copy, Move, Rename, Delete etc). You can also get single property of any Azure blob or get list of blobs as ADO.net Table and which can be easily loop through using ForEachLoop Task. Most of operations in this task support recursive search using wild card pattern (e.g. Myfile*.txt). Azure Cloud Storage service is one of the most popular Cloud Storage Services available right now.
|
Account Name: mystorageaccount Access Key: Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==
Note: If you want to download multiple files then use wild card pattern as below (when you use wild card pattern in source path then system will treat target path as folder regardless you end with slash )
Examples:
mybucket/myfile123.txt (single file)
mybucket/myfile*.txt (all files starting with myfile)
mybucket/mysubfolder1/myfile*.txt (all files starting with myfile and located under folder mysubfolder1)
| Property Name | Description | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| StorageAction | This property determines what action needs to be performed on Azure Storage object(s).
|
||||||||||||||||||||||||||||
| Permission | This property determines what permission is applied on selected container path. This option is only applicable for SetContainerPermission action. | ||||||||||||||||||||||||||||
| AutoSetContentType | When this option is True (default is false) it will automatically determines content type of the file during Upload, Create, Copy and Move operation.Default content type of any new file is application/octet-stream | ||||||||||||||||||||||||||||
| UserDefinedContentTypes | If content type is not correctly set for certain file extension then you may provide your own list of content types. It has to be in the following format. Each content type in new line
*.csv=text/plain *.dtsx=text/plain *.dtsconfig=text/plain ..... ..... |
||||||||||||||||||||||||||||
| DefaultContentType | By default every new file on Azure Storage is set to content type application/octet-stream. If this is not what you want as default content type then you may provide UserDefinedContentTypes or just set this property to your own content type. System set content type in the following order
1. Check if UserDefinedContentTypes is set (if extension of file being processed match with user defined content type list then set file content Type) 2. Check if AutoSetContentType is set (if True then Lookup Predefined list and set file content Type) 3. Check if Check DefaultContentType is set (if DefaultContentType is set then use it for file being processed) |
||||||||||||||||||||||||||||
| ThrowErrorIfSourceNotFound | If this option is set to True (default is true) then when source path not found on Azure Storage exception is thrown. If this option is False then no error is thrown when file not found. | ||||||||||||||||||||||||||||
| DeleteSourceWhenDone | If this option is set to True (default is false) then after successful upload/download operation source file is deleted. This option is applicable only for Upload and Download actions. | ||||||||||||||||||||||||||||
| MetadataKeyValue | For any new file you may specify metadata in Key/Value pair from Metadata Tab of task UI. If you want to enter from expression then use the following syntax for multiple Key/value pairs of metadata. This option is ignored if EnableMetadataUpdate=False (see next option).
<MetaData><User>SamW</User><Host>BW001-DEV</Host></MetaData> |
||||||||||||||||||||||||||||
| EnableMetadataUpdate | If this option is True (default is False) then MetadataKeyValue property is processed (see previous option) | ||||||||||||||||||||||||||||
| SourceStorageConnection | Connection Name for Source S3 Connection (Not applicable for UploadFileToAzure and CreateNewAzureFile operations) | ||||||||||||||||||||||||||||
| SourcePathAccessMode | Specifies how source path will be provided | ||||||||||||||||||||||||||||
| SourcePathValue | Direct static value (only applicable when SourcePathAccessMode=Direct) | ||||||||||||||||||||||||||||
| SourcePathVariable | Variable Name (only applicable when SourcePathAccessMode=Variable) | ||||||||||||||||||||||||||||
| SourcePathConnection | Connection Name or ID (only applicable when SourcePathAccessMode=Connection) | ||||||||||||||||||||||||||||
| Recursive | Specifies whether source path pattern should scan sub folder (default false) | ||||||||||||||||||||||||||||
| ResultVariable | Variable name which will hold result from S3 Action (default false) | ||||||||||||||||||||||||||||
| TargetStorageConnection | Connection Name for target S3 Connection (Only applicable for UploadFileToAzure, CopyAzureFileToAzure, MoveAzureFileToAzure and CreateNewAzureFile operations) | ||||||||||||||||||||||||||||
| TargetPathAccessMode | Specifies how target path will be provided | ||||||||||||||||||||||||||||
| TargetPathValue | Direct static value (only applicable when TargetPathAccessMode=Direct) | ||||||||||||||||||||||||||||
| TargetPathConnection | Connection Name or ID which points to target file path (only applicable when TargetPathAccessMode=Connection) | ||||||||||||||||||||||||||||
| TargetPathVariable | Variable Name (only applicable when TargetPathAccessMode=Variable) | ||||||||||||||||||||||||||||
| OverWriteOption | Specifies whether overwrite action when target file exists | ||||||||||||||||||||||||||||
| CreateMissingTargetFolder | Create target folder if missing (default True). This option is applicable for DownloadAzureFileToLocal | ||||||||||||||||||||||||||||
| ExcludeRegXPattern | Regular expression pattern to exclude items from selection (if you apply MyFile*.* filter for source path and then use ExcludeRegXPattern=(.msi|.exe) then it will include all files with matching name but exclude *.msi and *.exe | ||||||||||||||||||||||||||||
| IncludeRegXPattern | Regular expression pattern to include items from selection (if you apply MyFile*.* filter for source path and then use IncludeRegXPattern=(.txt|.csv) then it will include only txt and csv files with matching name pattern | ||||||||||||||||||||||||||||
| SortBy | Property by which you want to sort result
|
||||||||||||||||||||||||||||
| SortDirection | Sort order (e.g. Ascending or Descending)
|
||||||||||||||||||||||||||||
| WhereClause | Where clause expression to filter items. (e.g. Size>100 and Extension IN ('.txt','.csv') ) | ||||||||||||||||||||||||||||
| EnableSort | Sort items by specified attribute | ||||||||||||||||||||||||||||
| MaxItems | Maximum items to return (e.g. TOP) | ||||||||||||||||||||||||||||
| ContinueOnError | This option prevents task failure on error such as transfer of file(s) failed for some reason, delete operation failed for few files. | ||||||||||||||||||||||||||||
| LoggingMode | Determines logging detail level. Available options are Normal, Medium, Detailed, Debugging |
|
Download the Latest File from Azure Blob Storage using SSISIntroduction In our previous article, we saw SSIS Azure Blob Storage task examples. Now let’s look at how to Download the Latest File from Azure Blob Storage using SSIS. Microsoft SSIS includes the Azure Blob Storage Task that allows us to upload files to Azure Blob Storage, download files, creating local and remote directories an more. […] |
|
|
SSIS Azure Blob Storage Task Examples (Download, Upload, Delete files / folders)Introduction In this article, we will show SSIS Azure Blob Storage task examples. Uploading files using Azure Blob Storage Task or downloading files are common requirements in SSIS. In order to work with Azure Blob Storage, Microsoft SSIS includes the Azure Blob Storage Task that allows to upload files to Azure Blob Storage, download files, creating […] |
|
|
Read Azure Blob Storage Files in SSIS (CSV, JSON, XML)Introduction In our previous blog we saw how to load data into Azure Blob Storage. Now in this blog, we will see How to read Azure Blob Storage Files in SSIS (CSV, JSON, XML Format files). To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to multiple destinations like […] |
|
Setting Metadata key / value in SSIS Amazon S3 / Azure Storage TaskIntroduction In this post we will see how to set Metadata key/value in Azure Blob Storage Task and Amazon Storage Task (S3 Task). We will also see how to set them dynamically at runtime (Use SSIS Expression). Set Amazon S3 Task Metadata (Key / Value Pairs) Here is how you can set Metadata in Amazon […] |
|
SSIS Data Load – SQL Server to Azure Blob (Split Files, GZip)Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to Azure Blob Storage using SSIS. We will export / compress data to multiple files. For demo purpose we will use SQL Server as relational source but you can use same steps for any […] |
|
How to download files from Azure Blob Storage in SSISIntroduction In this blog, we will show how to download files from Azure Blob Storage using SSIS. To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to multiple destinations like flat files, Azure, AWS, databases, Office files and more. They are Coding free, drag and drop high-performance suite of Custom SSIS […] |
|
Using Regular Expressions in SSISIntroduction In this short article you will learn how to write Regular expressions in SSIS (i.e. Regex) and what tool to use to test them. You will also find helpful resources on how to write more sophisticated expressions and learn more about them. For demo purpose we will use FREE SSIS Regex Parser Task to parse and […] |
|
Filter by file size, name, date for Amazon / Azure Storage SSIS TaskIntroduction Sometimes you may need to make an advanced filtering on file path, file date, size etc in SSIS Amazon S3 Task or SSIS Azure Blob Storage Task . For example, you have a directory “Customers“, which contains client directories. Each of them contains a directory named “Invoices“, which is partitioned by year. E.g. “Customers/Microsoft/Invoices/2015”. So […] |
|
SSIS – Copy Amazon S3 files from AWS to AzureIntroduction Azure and AWS both are most popular Cloud Platforms. In this blog post we will learn how to copy or move Amazon S3 files to Azure Blob Storage without any coding or scripting (AWS to Azure File Copy / Migration Scenario). To achieve this objective we will use following Drag and Drop SSIS Tasks (i.e. […] |
