SSIS Flat File Source Text Qualifier being ignored

Question

I am using SSIS to insert data from flat file to database.

I have created Data Flow Task for that. I am using Flat File as Source and ADO NET Destination to insert data.

Below is how my setting looks like for Flat File Source.

Below is how my "Columns" tab look like

THIS WORKS FINE WHEN I RUN THAT USING BIDS AND DATA IS INSERTED PROPERLY INTO DATABASE. IT EVEN WORKS WITH DTEXEC.EXE WHEN RUN LOCALLY.

Now, The problem is with executing the package on the server using dtexec.exe. On the server, data is inserted properly but the text qualifier (double quotes) given is totally ignored while inserting data to database. THE SAME WORKS TOTALLY FINE WHEN RUN LOCALLY. I have attached image below for how its stored in database.

I have checked SQL SERVER version and SSIS version locally and on remote server and both are same.

What can be the problem? Can anyone help?

Answer 1

So I found a solution for this problem. Thanks to LukeBI answer here

Create a string variable called TextQualifier and assign the value " (double quotes)

Select the connection manager, and in the Properties window select 'Expressions'. See below.

Click ..., add the property 'TextQualifier' and assign the variable @[User::TextQualifier]. See below image

Now its working fine. It will even work on 64 bit OS now.