How to detect missing columns in SSIS

How to detect extra columns or missing columns

ssis reg expression taskSometimes, we need to validate the input columns to check if there are some extra columns or missing columns in SSIS. ZappySys includes some pretty nice tools, but some of them cannot check the required values. To fix that problem, you can complement these tools with our Regular Expression Parser Task.

We will use Regular Expressions to validate the columns. For example, we want to check that the file has 3 columns with specific names, if it contains extra columns or there are missing some columns, we want to send an error. We will show now how to do this.

We will be using this ZappySys SSIS PowerPack component to make things work:

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Creating a CSV file

  1. In order to start, we will create a CSV file with some columns. Create a file named document.csv or any name of your preference.
  2. Add data similar to the following:

     

Create a connection in SSIS

  1. First of all, SSDT, open an SSIS project.
  2. Secondly, in the connection manager, right-click and select a new file connection.
    Create a new file connection

    SSIS file connection

  3. In the Connection, select the CSV file created before:
    Select a file connection in SSIS

    ssis select file connection

 

ZS Regular Expression Parser to detect missing columns in SSIS

  1. Now, it is time to use the ZS Regular Expression Parser.
  2. Secondly, drag and drop the ZS Regular Expression in the design pane:
    Regex SSIS task

    SSIS Regular Expression parser

  3. Also, open the task and select the connection in the ZS Regular Expression task.
  4. In addition, add the following expression in the Task:
  5. In the previous scenario, the expression will fail because it is missing the email.
  6. Finally, the following Expression will pass, because the email is included.
    Verify the missing columns in SSIS

    SSIS verify missing columns in SSIS

Conclusion

In this article, we learned how to validate and check the columns in a file in ZappySys using the ZS Regular Expression Parser. In the example, we created an expression to detect an exact match of columns. It will raise an error in case of a failure.

If you want to enjoy this and other ZappySys tools, you can download our product here.

Posted in SSIS Tips & How-Tos and tagged , , .