SSIS Flat File Source Text Qualifier being ignored

5
votes

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.

Flat File Settings

Below is how my "Columns" tab look like

ColumnsTab

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.

Data Inserted into 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?

3
sql-serverssis
What does the raw data look like in notepad? – Eric Hauenstein
@EricHauenstein: It looks like " 11111118","Test ","Test ","04/08/1997","12/31/2018" – Sanjay Panchal

3 Answers

4
votes

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.

Expressions property

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

Add Property

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

1
votes

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!

1
votes

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.

Notice it is false in this example