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
Thank you so much for providing information and highlighting about the best possible usages of finding and applying string functions.
ReplyDeleteSSIS Postgresql Write
Hi, is it possible to improve the script so that it takes care of sql injection ?
ReplyDeletePs, a big fan of all you do <3
Nice article and fastidious arguments commented here, I am in fact enjoying by these. 789bet vip
ReplyDeleteThanks for sharing this post. I got information on this post. Keep sharing.
ReplyDeletelawyers for bankruptcies near me