you learn something new every day

I have an SSIS package that imports data from various comma-delimited text files that use quotes as the text delimiter.

The package works fine on my local machine, however when I deploy it to the server, the quote delimiter is ignored and all my data contains the quotes around the text. Worse, integer fields fail to convert as to SSIS it looks like the field is a string (“1”) rather than an integer (1).

Turns out that because the filenames of the text files are specified at runtime (they come from a variable in the database), the text delimiter field in the text file connection is also ignored. I specified the delimiter as an expression (I had to use TRIM(“\””) as the expression as it was the only way to get it in there). Bingo! Everything works perfectly on the server.