Derived Column with backslash error/issue

Question

I have searched a few hours on this issue and have not found anything specific to my issue. If there is, please direct me to any prior link.

I have a csv file with 13k rows. There are 2 random rows in this file with a backslash that someone manually entered into a free text field. Both of these backslash appearances are found inside one particular column that is 4th from the end of the file. The first appearance of this backslash occurs on line 2600. When I run my SSIS package with this backslash, it fails. When I manually delete the backslash in the actual csv file prior to running my package, it runs fine. So, I created a Derived Column in my package that would replace the backslash and eliminate any manual work on my part. I am using the following expression:

Replace(Client_Column, "[\]","")

When I run the package with this expression, it still fails. I receive the following error: "The Column Delimiter for column "Client_Column" was not found.

Now, when I go into the CSV file and move the row with the backslash from row 2600 to row 4, the package runs perfectly. Any idea what is happening? Am I doing something wrong? Am I better off just manually deleting the backslashes in the csv file prior to running the package?

Thanks

Answer 1

When you open the file and save it. The file may replace some settings with your IDE settings. Encoding?

Yes, that is a likely explanation. Another possibilities are:

• Removing trailing spaces.
• Replacing tabs with spaces.
• Changing newlines to CR-LF.
• Adding missing line break at the end.

Rather saving the file over the old file that does not work, save to a new file and verifies that it works. Then use a comparison tool find out the difference. I would recommend Beyond Compare. No, I am no way affiliated with them, but I'm a very happy user of Beyond Compare. The have comparisons for all sorts of files - including binary.

Answer 2

The backslash should not be the problem for your CSV file. Could you mock up a sample file to reproduce this issue? Thanks.

Answer 3

Hi @Frank Sweeney

If you want to replace the backslash in SSIS package, the expression should be like below:

REPLACE(Client_Column,"\\","")

Please have a double check.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.