CODEWITHSUNDEEP
ssis
0537
0537

Reputation: 1033

Source File Validation in SSIS

I need to validate a source input File based on below Data Quality indicators

1. The record Count of the Source File must be greater than 0
2.columns "id" and "AsofDate", should not contain any null values.
3. The “AsOfDate” column Value must be equal to the Reporting Date (Input Parameter to Job).
4. Based on the above Indicators, log these information in either some text file or database table.

Am Attaching the sample Source input File

enter image description here

Upvotes: 1

Views: 3614

Answers (1)

David Benham
David Benham

Reputation: 1174

A simple solution is to use a Data Flow Task and add a variable to your solution: RowCount

In your data flow task, you could use the following items:

  1. Flat File Source
  2. Row Counter
  3. Derived Column
  4. Conditional Split
  5. Destination

Flat File Source

Create your typical flat file data source

Row Counter

Add a counter and set the variable of the counter equal to your row count variable.

Derived Column

I'd rather do expression logic in a derived column

  • New Column 1: If RowCount > 0 return true else false
  • New Column 2: If ID is null or AsOfDate is null return false else true
  • New Column 3: If AsOfDate = InputDate return true else return false

Conditional Split

Since your new columns are booleans, you should be able to do something like as follows:

Output 1:
Column1 && Column2 && Column3

Destination

Connect Output 1 from the conditional split to the destination of your choice.

Upvotes: 1

Related Questions