SSIS data validation

Posted on 2007-08-07
Last Modified: 2008-01-09

I'm working on a project that will load data from a text file into the database.  I've created an SSIS package that loads the data into staging tables and then I have a sql statement that inserts the data into the correct production tables.  

My question is what is the best way to validate the data using SSIS?  
i) Invalid or missing values (e.g. bad dates. non-numeric data in numeric fields) and then produce an exception report.  

Please Help as I need to have this done soon!
Question by:tracimcp
    LVL 8

    Expert Comment

    You have two options.  You can manually add tasks that massage the data, and look for "invalid data", and have it put these items in an audit report.

    Or you could use the Error pages within your tasks to decide what to do w/ errors when they are encountered.  To connect another task to an "error", it's always the red line.  Then have that sub-task put the results in an audit report of sorts.

    Author Comment

    One of the columns is a date field and is being sent to us in the format: yyyymmdd.  How  can i determine if an invalid date is in that field? An example would be if some of the date field was missing:  200707?

    LVL 8

    Accepted Solution

    You could add a Conditional Task, or you could attempt to copy the data (using Copy Column) into a new DateTime column.  If it fails you'll know it was an invalid date.

    Author Comment


    I would prefer to use the conditional split.  But what would I put in the field to check the date if I use the split?


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now