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