How to replace double quotes in derived column transformation?

by

I think you have almost got the expression correct except for the additional slash in the replacement string. Here are the expressions that might work for you.

Expression #1: Removes all double quotes within a given string.

REPLACE(COLA, "\"", "")

Expression #2: Replaces all double occurrences of double quotes with single occurrence of double quotes.

REPLACE(COLA, "\"\"", "\"")

Here is an example that demonstrates expression #1:

  1. Screenshot #1 shows the CSV file that will be read by a package.
  2. Screenshot #2 shows the Derived Column transformation inside the Data Flow task that will replace all double quotes within the first column named as Header.
  3. Screenshot #3 shows data in the table after the package execution. Notice that the double quotes in second column are left as it is because there is no expression to replace them.

Here is an example that demonstrates expression #2:

  1. This example will use the same file as in example 1. Refer screenshot #1.
  2. Screenshot #4 shows the Derived Column transformation inside the Data Flow task that will replace all double occurrences of double quotes with single occurrence of double quotes within the first column named as Header.
  3. Screenshot #5 shows data in the table after the package execution. Notice that the double quotes in second column are left as it is because there is no expression to replace them.

Hope that helps.

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

More Related Contents:

  1. Import most recent csv file to sql server in ssis
  2. How to create a temporary table in SSIS control flow task and then use it in data flow task?
  3. SSIS – How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename
  4. Parameterized OLEDB source query
  5. Microsoft.ACE.OLEDB.12.0 is not registered
  6. SSIS how to set connection string dynamically from a config file
  7. How to load a flat file with header and detail data into a database using SSIS package?
  8. Insert a single row and return its primary key
  9. How to read a flatfile with lowercase thorn as the delimiter
  10. Export Varbinary(max) column with ssis
  11. How to send the records from a table in an e-mail body using SSIS package?
  12. SSIS Package Not Running as 32bit in SQL Server 2012
  13. 2 Data Flow Tasks Linking to one Execute SQL Task
  14. The value violated the integrity constraints for the column
  15. How to convert string in format yyyyMMdd to date using SSIS expression?
  16. How to import a fixed width flat file into database using SSIS?
  17. SSIS 2012 – Insert new rows, ignore existing rows
  18. SSIS: script task (vs15) not work when deploy on sql server 2014
  19. How to import text files with the same name and schema but different directories into database?
  20. How to make an HTTP request from SSIS?
  21. SSIS Task for inconsistent column count import?
  22. Query a database based on result of query from another database
  23. Running an SSIS Package using dtexec
  24. SSIS Source Format Implicit Conversion for Datetime
  25. How to Map Input and Output Columns dynamically in SSIS?
  26. Importing excel files having variable headers
  27. What are the differences between Merge Join and Lookup transformations in SSIS?
  28. Debugging SSIS Script task – Breakpoint is enabled but it does not hit
  29. How to execute dtsx packages through command line
  30. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher

Leave a Comment