Examples with Validator task

Introduction

In SQL Server Integration Services (SSIS), validating expressions, paths, and URLs is a crucial part of ensuring the integrity and success of your data integration processes. In this article, we’ll explore how the Validator Task can help you achieve this.

Before we dive into practical examples, be sure to check our comprehensive Validator Task article to discover the full range of options it offers. You can access it here.

Validating an Example

Let’s start by validating an example stored in a variable. You can source your example data from a variety of components, such as the Set Variable Transformation (see our article here) or the REST API Task (read more here). Once you have the example data in a variable, the Validator Task comes into play. You can configure the necessary expressions, as demonstrated in this example.

Configurate the validator task to compare two string.

Validating a File or a Folder

In some cases, you may need to validate a path, which might involve checking file or folder properties, such as their existence or size. Our advanced File System Task or SFTP Task can help you accomplish this. In our example, we’ll show you how to count the number of files in a folder and trigger a pass condition when there are more than five files.:

We employ a wildcard to retrieve all TXT files from the folder and store the result in a variable called “count.”

Validate the result: Next, we’ll drag and drop the Validator Task and select “Check SSIS expression (true or false).” We’ll use the expression @[User::count]==3 to validate whether we have 3 files in that folder. We’ll set the expected result as true, and we’ll also check the option to save the validator status to a variable and throw an error if the validator fails to terminate the package.

You can validate the expressions with the result you want (true/false)

You will receive a valid example, and the package will run successfully, or it will fail, as shown in this example:

Example from success and a failure example

You can use more examples, such as checking if a file exists or verifying the existence of a folder using the Advanced File System, Validator Task or other components.

Some examples for advanced file system task and Secure FTP task

Validating a URL

There are scenarios where the success of a URL is critical. If a URL fails, you may want your SSIS process to stop. Here’s an example of how to set up a REST API Task to handle errors, save the result in a variable, and use the Validator Task to mark a URL as invalid in case of failure.

Use this option to continue on any error and save the result in a Boolean variable

Now for validator task we set up the next configuration:

Validate if the URL is valid

The last step is running the package. Here you will see the two cases.

Comparing both results if the URL is valid or not

Conclusion

In conclusion, the Validator Task in SSIS empowers you to dynamically choose the path you need based on the results of previous tasks. This flexibility can significantly enhance the efficiency of your data integration processes, ultimately saving you time and ensuring data integrity.

For additional information, you can check articles for Advanced file system task, Secure FTP task and Validator task.

You can apply this approach to other components where you need to capture a result in a variable and then use the Validator Task.