If you have daily, weekly or even monthly loads it is worth considering some automation of you operations.
With Flat File Checker (FlaFi) console application you can execute validation automatically from batch file.
If you have an application that will run the load in the database then the whole process can be automated.
Create the Schema
When all rules are created for you schema through FlaFi GUI, make sure that "Save Valid Records to" field in "File Definition" form is correctly populated for all files.
The recommended approach will be to have a separate root folder (i.e.: <path>/Valid/) for valid records which should be emptied before or after each process execution.
FlaFi will populate this folder with files that contain valid records only.
Batch File
If FlaFi is installed to C:\Program FIles\Flat File Checker\ then you can call file validation like this:
- Code: Select all
DIR C:\Program FIles\Flat File Checker\
FlaFi.exe -silent <Schema path>
If FlaFi execution did not complete successfully ERRORLEVEL will be set to on of these values:
- 1 - Data Error
- 2 - Execution Error
- 3 - Schema File Error
- 4 - Execution cancelled
Data import must be done only in case if ERRORLEVEL=0, which means that validation completed and no errors were found.
Email Notification
As one fits all solution for email notifications I recommend to use CDO library from VBScript.
Create SendErrorMessage.vbs file that can be called to send email notification:
- Code: Select all
' Script to send error message if MS Access ended with error
' Parameters (zero-based array):
' 0) From
' 1) To
' 2) Subject
' 3) Message Body
Option Explicit
Dim objEmail
Set objEmail = CreateObject("CDO.Message")
objEmail.From = WScript.Arguments(0)
objEmail.To = WScript.Arguments(1)
objEmail.Subject = WScript.Arguments(2)
objEmail.Textbody = WScript.Arguments(3)
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"<smtpserver ip or name>"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Set objEmail=Nothing
Join Scripts
Now we need to create a Batch file that will call console version of FlaFi and then send notification depending on the error level:
- Code: Select all
DIR C:\Program FIles\Flat File Checker\
FlaFi.exe -silent <Schema path>
IF ERRORLEVEL 5 (
SendErrorMessage.vbs <to@address.com> <from@address.com> <Time out subject> <body>
GOTO DoExit
)
IF ERRORLEVEL 4 (
SendErrorMessage.vbs <to@address.com> <from@address.com> <Canceled subject> <body>
GOTO DoExit
)
IF ERRORLEVEL 3 (
SendErrorMessage.vbs <to@address.com> <from@address.com> <Schema Error subject> <body>
GOTO DoExit
)
IF ERRORLEVEL 2 (
SendErrorMessage.vbs <to@address.com> <from@address.com> <Execution Error subject> <body>
GOTO DoExit
)
IF ERRORLEVEL 1 (
SendErrorMessage.vbs <to@address.com> <from@address.com> <Data Errors subject> <body>
GOTO DoExit
)
IF ERRORLEVEL 0 (
SendErrorMessage.vbs <to@address.com> <from@address.com> <Success subject> <body>
GOTO DoExit
)
DoExit:
Exit
Windows scheduled task
Set up windows scheduled task to run the script.