Tag: Copy Activity

Posted on

Azure Data Factory or Synapse Copy Activity with File System

In Azure Data Factory (ADF) or Synapse, using Copy Activity with a File System as a source or sink is common when dealing with on-premises file systems, network file shares, or even cloud-based file storage systems. Here’s an overview of how it works, key considerations, and steps to set it up.

Key Components and setup with File System:

Create a File System Linked Service

Linked Service: For on-premises or network file systems, you typically need a Self-hosted Integration Runtime (SHIR).

Fill in the required fields:

  • Connection: Specify the file system type (e.g., network share or local path).
  • Authentication: Provide the appropriate credentials, such as username/password, or key-based authentication.
  • If the file system is on-premises, configure the Self-hosted Integration Runtime to access it.

Create File System Dataset

Go to Datasets in ADF and create a new dataset. Select File System as the data source.

Configure the dataset to point to the correct file or folder:

  • Specify the File Path.
  • Define the file format (e.g., CSV, JSON, XML).
  • Set any schema information if required (for structured data like CSV).

Considerations:

  • Integration Runtime: For on-premises file systems, the Self-hosted Integration Runtime (SHIR) is essential to securely move data from private networks.
  • Performance: Data transfer speeds depend on network configurations (for on-prem) and ADF’s parallelism settings.
  • File Formats: Ensure proper handling of different file formats (e.g., CSV, JSON, Binary etc.) and schema mapping for structured files.
  • Security: Ensure credentials and network configurations are correctly set up, and consider encryption if dealing with sensitive data.

Common Errors:

  • Connection issues: If the SHIR is not correctly configured, or if there are issues with firewall or network settings, ADF may not be able to access the file system.
  • Permission issues: Ensure that the correct permissions are provided to access the file system (file share, SMB, FTP, etc.).

Posted on

Comparing the use of wildcards in the Copy Activity of Azure Data Factory with the Get Metadata activity for managing multiple file copies

In Azure Data Factory (ADF), both the Copy Activity using wildcards (*.*) and the Get Metadata activity for retrieving a file list are designed to work with multiple files for copying or moving. However, they operate differently and are suited to different scenarios.

Copy Activity with Wildcard *.*

  • Purpose: Automatically copies multiple files from a source to a destination using wildcards.
  • Use Case: Used when you want to move, copy, or process multiple files in bulk that match a specific pattern (e.g., all .csv files or any file in a folder).
  • Wildcard Support: The wildcard characters (* for any characters, ? for a single character) help in defining a set of files to be copied. For example:
    • *.csv will copy all .csv files in the specified folder.
    • file*.json will copy all files starting with file and having a .json extension.
  • Bulk Copy: Enables copying multiple files without manually specifying each one.
  • Common Scenarios:
    • Copy all files from one folder to another, filtering based on extension or name pattern.
    • Copy files that were uploaded on a specific date, assuming the date is part of the file name.
  • Automatic File Handling: ADF will automatically copy all files matching the pattern in a single operation.

Key Benefit: Efficient for bulk file transfers with minimal configuration. You don’t need to explicitly get the file list; it uses wildcards to copy all matching files.

Example Scenario:

  • You want to copy all .csv files from a folder in Blob Storage to a Data Lake without manually listing them.

2. Get Metadata Activity (File List Retrieval)

  • Purpose: Retrieves a list of files in a folder, which you can then process individually or use for conditional logic.
  • Use Case: Used when you need to explicitly obtain the list of files in a folder to apply custom logic, processing each file separately (e.g., for-looping over them).
  • No Wildcard Support: The Get Metadata activity does not use wildcards directly. Instead, it returns all the files (or specific child items) in a folder. If filtering by name or type is required, additional logic is necessary (e.g., using expressions or filters in subsequent activities).
  • Custom Processing: After retrieving the file list, you can perform additional steps like looping over each file (with the ForEach activity) and copying or transforming them individually.
  • Common Scenarios:
    • Retrieve all files in a folder and process each one in a custom way (e.g., run different processing logic depending on the file name or type).
    • Check for specific files, log them, or conditionally process based on file properties (e.g., last modified time).
  • Flexible Logic: Since you get a list of files, you can apply advanced logic, conditions, or transformations for each file individually.

Key Benefit: Provides explicit control over how each file is processed, allowing dynamic processing and conditional handling of individual files.

Example Scenario:

  • You retrieve a list of files in a folder, loop over them, and process only files that were modified today or have a specific file name pattern.

Side-by-Side Comparison

FeatureCopy Activity (Wildcard *.*)Get Metadata Activity (File List Retrieval)
PurposeCopies multiple files matching a wildcard pattern.Retrieves a list of files from a folder for custom processing.
Wildcard SupportYes (*.*, *.csv, file?.json, etc.).No, retrieves all items from the folder (no filtering by pattern).
File SelectionAutomatically selects files based on the wildcard pattern.Retrieves the entire list of files, then requires a filter for specific file selection.
Processing StyleBulk copying based on file patterns.Custom logic or per-file processing using the ForEach activity.
Use CaseSimple and fast copying of multiple files matching a pattern.Used when you need more control over each file (e.g., looping, conditional processing).
File Count HandlingAutomatically processes all matching files in one step.Returns a list of all files in the folder, and each file can be processed individually.
EfficiencyEfficient for bulk file transfer, handles all matching files in one operation.More complex as it requires looping through files for individual actions.
Post-Processing LogicNo looping required; processes files in bulk.Requires a ForEach activity to iterate over the file list for individual processing.
Common Scenarios– Copy all files with a .csv extension.
– Move files with a specific prefix or suffix.		– Retrieve all files and apply custom logic for each one.
– Check file properties (e.g., last modified date).
Control Over Individual FilesLimited, bulk operation for all files matching the pattern.Full control over each file, allowing dynamic actions (e.g., conditional processing, transformations).
File Properties AccessNo access to specific file properties during the copy operation.Access to file properties like size, last modified date, etc., through metadata retrieval.
Execution TimeFast for copying large sets of files matching a pattern.Slower due to the need to process each file individually in a loop.
Use of Additional ActivitiesOften works independently without the need for further processing steps.Typically used with ForEach, If Condition, or other control activities for custom logic.
Scenarios to Use– Copying all files in a folder that match a certain extension (e.g., *.json).
– Moving large numbers of files with minimal configuration.		– When you need to check file properties before processing.
– For dynamic file processing (e.g., applying transformations based on file name or type).

When to Use Each:

  • Copy Activity with Wildcard:
    • Use when you want to copy multiple files in bulk and don’t need to handle each file separately.
    • Best for fast, simple file transfers based on file patterns.
  • Get Metadata Activity with File List:
    • Use when you need explicit control over each file or want to process files individually (e.g., with conditional logic).
    • Ideal when you need to loop through files, check properties, or conditionally process files.
Proudly powered by WordPress