Text Qualifier in SSIS

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 SSIS 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 1

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.

Text Qualifier in SSIS 2

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.

Text Qualifier in SSIS 3

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

Text Qualifier in SSIS 4

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.

Text Qualifier in SSIS 5

Let us check the data by visiting Columns Tab in Flat File Connection Manager

Text Qualifier in SSIS 6

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

Text Qualifier in SSIS 7

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.

Text Qualifier in SSIS 8

Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Text Qualifier in SSIS 9

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 SSIS Tutorials database

Text Qualifier in SSIS 10

Click on the Mappings tab to check whether the source columns exactly mapped to the destination columns.

Text Qualifier in SSIS 11

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

Text Qualifier in SSIS 13

Comments are closed.