Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2915
  • Last Modified:

SSIS data validation

Hello,

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!
0
tracimcp
Asked:
tracimcp
  • 2
  • 2
1 Solution
 
davecorunCommented:
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.
0
 
tracimcpAuthor Commented:
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?

Thanks!
0
 
davecorunCommented:
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.
0
 
tracimcpAuthor Commented:
Dave,

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?

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now