Post reply

SQL StoredProcedure for data validation using REGEX in SSIS

  • September 16, 2021 at 3:23 pm

    #3930199

    hi,

    I have a requirement where we get data from Oracle source and destination is also Oracle db. Only validation is done in SSIS. So, i have to validate the data using regular expression  in SQL Storedprocedure (in ssis) and send bad data to a error log table with logging the record ( with table name , column name , error code) .

    I am new to regular expression ,  i am kind of confused how to start and how to validate with regular expressions.

    Can you please provide simple , sample example to understand.

    For example : Age validation : Age cannot be zeros , age cannot be 200 years old or in future.

    How do we get simple Storedprocedure for the same?

     

    Thanks again for help in advance.

     

     

    • This topic was modified 2 years, 9 months ago by  komal145.

  • September 16, 2021 at 6:05 pm

    #3930258

    • This is a Microsoft SQL Server forum, and a SQL Server 2019 - Development channel.
    • TSQL does not support regex natively, & therefore you probably won't do regex in a TSQL stored procedure unless you implement CLR functions. PLSQL does have some regex functions. But you really do not need regex for the simple criteria you cited.

    In this case the where clause filter would seem to simply be:

    Age > 0 AND Age < 200

    You stated "Age cannot be zeros". I hope that does not mean age is a text/varchar datatype that could include a string of multiple zeroes.

    In future does not make sense to me in the context of age. What do you mean when you say age cannot be "in future"?

  • September 16, 2021 at 9:47 pm

    #3930315

    I'll also state that RegEx is serious overkill for the type of validations you've described.  You certainly don't need CLR, either.  The example the ratback provided above will work quite nicely and usually faster than a call to RegEx via a CLR (been there, done that).  It's just not necessary for thing like you describe.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 8, 2021 at 4:34 pm

    #3938916

    Thanks for the Reply. I was able to validate data using Regular expressions , since we have oracle source data Using Script component as transformation in SSIS. It is way easier to validate the data , record by record , to load data into Target. Replying this thread , as it might help someone.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply