The Text Qualifier in SSIS or How to remove Double Quotes in CSV file or How to remove Double Quotes in flat-file is the most common question asked in any Interview. In this article, we show you how to get rid of Double Quotes in CSV file using Text Qualifier in SSIS with a live example.
The below screenshot shows the data present in the Customers.txt flat file. If you observe, every row after the header section surrounded by the double quotes (” “). Let us see the SSIS Text Qualifier steps to resolve it
Text Qualifier in SSIS Example
STEP 1: Drag and drop the data flow task from the toolbox to control flow region and rename it as Text Qualifier in SSIS.
Double click on it, and it will open the data flow tab.
STEP 2: Drag and drop Flat File Source and OLE DB Destination from the toolbox to the data flow region.
Double click on Flat File Source in the data flow region will open the Flat File Source Editor to configure the connection manager settings. If you haven’t created Flat File Connection Manager before click on the New button.
Once you click on the New button, the Flat File Connection Manager Editor opened. Please click on the Browse button to select the required file from our file system. Here, we are choosing the Customers.txt flat file
Once you selected the file, we have to specify whether our text file holds column names in the first row or not by check-marking Column names in the first data row option. Since our flat-file contains the column names in the first row, we are check-marking the option. If your text file is different, then don’t select it.
Let us check the data by visiting Columns Tab in Flat File Connection Manager
SSIS Text Qualifier
Now, come back to General Tab and place double quotation mark in SSIS Text Qualifier property as shown below
TIP: You can replace this double quote mark with any special character to remove those special characters from flat file
Click OK will close the Flat File Connection Manager Editor. If you want to retain the Null values as Nulls, Please check mark Retain null values from the source as null values in the data flow option.
Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.
Click OK and drag and drop the Flat File Source output on to OLE DB Destination.
Now we have to provide the Server, database, and table details of the destination. So double-click on the OLE DB Destination and provide the required information. From the below screenshot, you can see that we are selecting Text Qualifier in SSIS table present in the database
Click on the Mappings tab to check whether the source columns exactly mapped to the destination columns.
Click OK to finish our SSIS text Qualifier package design. Let us run the package and Check the results whether we successfully removed Double Quotes in a flat file using text qualifier in SSIS or not