How to get String between two Underscores in SSIS Expressions - SSIS Real Time Example

 Issue: How to get String between two Underscores in SSIS Expressions - SSIS Real Time Example

In this article, we are going to learn how to get string between two underscores in SSIS expressions - SSIS real-time example, let's start our demonstration, we are going to get a name of a file that's between two underscores, that's going to lead us another scenario how to use these files in the same foreach loop and loading different data flows, that's a very common scenario we often getting a mix of the file and we need to load them into different data flows, let's open the Visual Studio, in my case I have Visual Studio 2017, go to the SSIS Toolbox at the left side of the dashboard, and search for foreach loop container and bring it into the working window and inside the foreach loop we are going to point where our files are, double click on the foreach loop container and go to the collection, inside the collection we have to select Foreach file Enumerator, then brows for the folder and select your file folder, then in the Files: tab we need only .txt files, and then select Name & Extention, as shown in the picture below.

Fig-1: Foreach Loop Editor configuration of collection.

Then go to the Variable Mappings, click on the variable and name the variable in my case it is ''FullFileName'', and then click ok, as shown in the picture below.

Fig-2: Foreach Loop Editor add varibale.

Once we are done with our foreach loop configuration, go to the SSIS Toolbox, and search for the Script Task and bring it into the foreach loop window, then bring the data flow task under the script task, then bring the second data flow task and then bring the third data flow task and connect all the data flow tasks with script task nd rename the data flow task with you file names, as shown in the picture below. 

Fig-3: Bring the data flow task under the Script task.

The next step is to write our expressions, go to the folder which you have your files and copy the file name of anyone, then paste it into the value column, and then create another variable, and I will name this variable as ''FileNameOnly'', and the data type will be string, then write the expressions by clicking the ellipsis button in front of our new variable, first of all, we need to use a substring, we need to get the data in the substring, then give the file name, and then mention from where I want to start my string, we have used another function which is FindString, so bring it in the expression, and inside the find string we have to describe from where we want to read our file name, etc, as shown in the picture below, once we write our expression then click ok.

Fig-4: write the expressions for our variables.

     

Once we are done with our expressions, now come back to our working window and then double click on our first data flow task in my case it is Product_File, and then click on the expression and click to select the expression, then select our variable which is FileNameOnly then add == ''Product_File'' and then click ok as shown in the picture below, do the same practice with our 2 other data flow tasks, which are Customer_File and Sales_File.

Fig-5: Add expressions into data flow task.

Once we are done with Expressions selection, now right-click on the package name and Execute the package, and then you can see the execution results, if there is any problem with the expressions or file name it will show an error, if everything goes right it will show the results as shown in the picture below. 

Fig-6: Successfully completed execution process.

Video Demo: How to get String between two Underscores in SSIS Expressions