Text Qualifier property in Flat File Source in SSIS

We are getting the multiple sales CSV files with the same structure from the different Clients. There is only one difference in the files. All files are the different Text Qualifiers. See below.

Client A File: This is the “ Text Qualifier.

 

Client B File: This is ‘ Text Qualifier.

   

Client C File : this is ~ Text Qualifier

  

Client D File : this is ` Text Qualifier  

Client E File : this is * Text Qualifier

 

And so on…

Read here:  Remove double quotes from CSV file in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/03/remove-double-quotes-from-csv-file-in.html

Drawback back of the above solution is that in this case we need to write 5 data flow task to load theses files. In case if any other client added again we need to add the new task to load this file.

To overcome with this issue we need to set the Text Qualifier property value dynamically.

In this Demo we will see the how to set the Text Qualifier Property dynamically.

Taking the data Flow task.

  

Now taking a variable to store the Text Qualifier.

 

Now we are taking the Flat File Connection Manager.

 

Click ok.

Now go the property of the FF_Conn

ExpressionàSelect Property as Text Qualifier.

 

Click on the Expression Icon.

  

Evaluate the expression.

Click ok.

After creating the expression we will see the Fx logo on this FF_Conn

 

Now we are taking the Source as flat file and Multicast transformation and the data viewer to see the records.

  

It is ready to run.

Running the Client A file

     

See the result.

  

Now We are running the file for the Clint B.

For the Clint B we are using the same tasking only we are changing the value of the variable.

File

  

Text Qualifier Value.

  

Now running the package.  

See one more file. 

Changing the variable value. 

Let’s run the package.

 

Get the expected result.

Hope this will help for such type of business requirement.