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.

Answer 2

In the flat file source, click "Columns". Make sure that when you preview the data there are no quotes in the preview. Otherwise you may have to look back at your file and make sure that BOTH the text qualifier and delimiter are correct.

If this does not work, then please take a screenshot of the "Columns" screen as well and post it. A screenshot of the actual file layout would help as well. Hope this helps!

Answer 3

Within your flat file connection Manager, within the "Advanced" option, you should be given a view of different parameters for each field. You will see for each field it will have a Name, ColumnDelimiter, a bunch of faded out fields and the DataType and a choice if it is text qualified or not.

In there, you should specify that the column(s) which you wish to be determined as text qualified by setting the TextQualified option to true.