How to Use FindString Function in Conditional Split Transformation ( How to Find Occurrence of a Character is more than one Time) - SSIS Tutorial

Scenario:

Consider a scenario in which we are getting records in a flat file and one of the column is Address. The address is separated by "," eg. MyStreet Address,MyCity,MyState. If we decided that if Address Column does not have at least two "," ( commas) in it, it is not complete address and we don't want to load that record.

Solution:

We will be using FindString Function in Conditional Split Transformation to solve this requirements

Step 1:

Create sample text file by using below data

ID|Name|Address
1|Aamir|Street Address,CityName,NC
2|Raza|Grove Street,Rio Rancho
3|Robert|Jewett Ave,Jersey City, NJ

Step 2: 

Create an SSIS Package and inside SSIS Package, Bring the Data Flow Task and then drag Flat File Source and make connection to your source file as shown below.


Fig 1: Flat File Connection Manager to Flat File in SSIS Package


Step 3:

Now bring the Conditional Split and write expression as shown below. we will be using FindString Function and we will check for second occurrence of "," in the Address. If Address does not have second comma then FindString is going to return us 0. That means the address is not valid as per our analysis or requirement and we will redirect that row to InvalidAddress Output and other output of Conditional Split Transformation will return us records in which address does has "," for value ( Correct Address).

Expression used in Conditional Split Transformation : 
FINDSTRING(Address,",",2) == 0


Fig 2: Use FindString in Conditional Split transformation to Find Occurrence of Character

Step 4:
I am using couple of Multicast and then Data Viewer after Conditional Split to show you the output. You can go ahead and map the outputs to your final destination.


Fig 3: How to Find Invalid Address by using FindString Function in SSIS Package

4 comments:

  1. Thank you so much for providing information and highlighting about the best possible usages of finding and applying string functions.

    SSIS Postgresql Write

    ReplyDelete
  2. Hi, is it possible to improve the script so that it takes care of sql injection ?
    Ps, a big fan of all you do <3

    ReplyDelete
  3. Nice article and fastidious arguments commented here, I am in fact enjoying by these. 789bet vip

    ReplyDelete
  4. Thanks for sharing this post. I got information on this post. Keep sharing.
    lawyers for bankruptcies near me

    ReplyDelete