Migrating SSIS File validation logic to ADF

Question

I work in an environment where inbound files are loaded into an on-prem SQL Server. We currently use SSIS to loop through these files, performing multiple validations to ensure they are correctly formatted. Our validation process follows a sequential approach:

1. A validation is performed on the file.
2. If the validation passes, we proceed to the next check.
3. If the validation fails, we mark the file as failed and execute a failure handling routine (e.g., sending notifications, moving the file to an error location).

Migration to Snowflake and ADF Constraints

We are migrating from on-prem SQL Server to Snowflake, requiring a transition from SSIS to ADF. However, ADF has significant limitations:

• No support for nested IF statements
• No support for IF conditions inside a ForEach loop

This makes it difficult to replicate our existing sequential validation logic. In SSIS, our package follows this structure:

1. Check File Name → If valid, proceed to the next step; otherwise, fail the file.
2. Check Header Format → If valid, continue; otherwise, fail the file.
3. Perform Additional Validations → Each step executes sequentially based on previous outcomes.
4. Determine File Type → Load the file using the appropriate process based on its type.

We need to achieve conditional sequential processing in ADF without writing an overly complex stored procedure in Snowflake.

Seeking a Solution

Given ADF constraints, what is the best approach to implement this sequential validation logic?

Answer 1

Instead of nesting IF conditions, you can chain multiple pipelines with dependencies to ensure that each validation occurs sequentially.

Example:

1. Pipeline 1: File Name Validation
   • Use a Lookup Activity to check if the file name is valid.
   • If valid, call Pipeline 2.
   • If invalid, move the file to an error location and trigger a failure notification.
2. Pipeline 2: Header Validation
   • Use Mapping Data Flow or a Lookup Activity to validate the header structure.
   • If valid, call Pipeline 3.
   • If invalid, move the file to an error location.
3. Pipeline 3: Content Validation
   • Perform additional row-level validation.
   • If valid, determine the file type and trigger the correct load process.

Another approach, you can use a Web Activity with an Azure Function or Logic App that evaluates the conditions dynamically and returns the next step.

Steps:

1. Use a Lookup Activity to get the file metadata.
2. Pass the metadata to an Azure Function that evaluates the validation rules.
3. The Azure Function returns a response (e.g., {"status": "pass", "nextStep": "headerValidation"}).
4. A Switch Activity in ADF determines whether to proceed to the next step or fail.

If you want to avoid multiple ADF pipelines, you can push the sequential validation logic into Snowflake stored procedures and call them from ADF.

Steps:

1. Create a stored procedure in Snowflake that:
   • Checks the file name.
     • Validates the header.
       • Validates row-level content.
         • Determines the correct load routine.
         1. In ADF, use a Stored Procedure Activity to execute this procedure.
         2. Capture the output (success or failure) and take the necessary action.

ApproachProsConsMultiple Pipelines & DependenciesEasier to maintain, visually clearRequires more pipelines, increases execution timeMultiple Pipelines & DependenciesEasier to maintain, visually clearRequires more pipelines, increases execution timeWeb Activity + Azure FunctionFlexible, logic remains dynamicRequires additional Azure services (Functions or Logic Apps)Stored Procedure in SnowflakeKeeps logic centralized in SnowflakeMight become complex with extensive procedural logic--- Recommendation

• If you prefer a visual approach, go with multiple pipelines.
• If you want a dynamic, scalable solution, use Web Activity + Azure Function.
• If you want a database-driven approach, use a Snowflake stored procedure.

Answer 2

Hey,

PFB the responses:

you can have IF activity within a For each activity

You can use the below flow :

within foreach loop (for file iterations)

1. use getmetadata activity to check get the file details from the path (filename, filetype,header etc)
2. use IF activity to check whether the filename is as expected or not
   1. if expected, no activities within True
   2. if not, then add a fail activity within False to throw error and fail the iteration
3. Use If activity to check whether the header is as expected or not
   1. True ,false would be same as above
4. Add the necessary additional steps
5. Depending on the file type use Switch activity to redirect for the necessary executions