Text Qualifier in SSIS or How to remove Double Quotes in csv file or How to remove Double Quotes in flat file are the most common question asked in any SSIS Interview. In this article we will show you, How to get rid of Double Quotes in csv file using Text Qualifier in SSIS with live example.
Below screenshot will show you the data present in the Customers.txt flat file. If you observe closely, every row after the header section is surrounded by the double quotes (” “). Let us see the steps to resolve it
Text Qualifier in SSIS 2014 Example
STEP 1: Drag and drop the data flow task from the toolbox to control flow region and rename it as Text Qualifier property in SSIS 2014
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 toolbox to data flow region.
Double click on Flat File Source in the data flow region will open the Flat File Source Editorto configure the connection manager settings. If you haven’t created Flat File Connection Manger before click on the New button.
Once you click on New button, Flat File Connection Manager Editor will be opened. Please click on the Browse button to select required file from our file system. Here, we are selecting the Customers.txt flat file as shown below
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 holds 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 Manger
Now, come back to General Tab and place double quotation mark in 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 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 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 observe that, we are selecting Text Qualifier in SSIS table present in SSIS Tutorials database
Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click OK to finish our package design. Let us run the package and Check the results whether we successfully removed Double Quotes in flat file using text qualifier in SSIS or not
Thank You for Visiting Our Blog
