5

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?

Sanjay Panchal
  • 540
  • 2
  • 8
  • 20

3 Answers3

4

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.

Sanjay Panchal
  • 540
  • 2
  • 8
  • 20
1

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!

Philip Devine
  • 1,169
  • 5
  • 11
  • I have added "Columns" tab to my question. And I already checked and there are no quotes when I see Preview tab. As I said, its working fine locally but gives problem on server. – Sanjay Panchal Jun 19 '15 at 13:08
1

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

Community
  • 1
  • 1
eddiecubed
  • 174
  • 1
  • 2
  • 12
  • I have already checked and **TextQualified** option is set to **true** for all columns. – Sanjay Panchal Jun 20 '15 at 04:19
  • 1
    Check to see if there is a difference between operating systems, explicitly if there's a difference in 32-bit or 64-bit. I have seen a similar issue with this exist because of the dev environment being a 32 bit have exact same problems while running on a 64 bit server. – eddiecubed Jun 20 '15 at 07:55
  • Yes, I have checked and looks like I have the same problem, 32 Vs 64 bit. When I ran the package using 64 bit **DTExec.exe** locally, it inserted wrong data (data with double quotes) whereas the same works fine when using 32 bit **DTExec.exe**. My server is set up for 64 bit. Can you please let me know what did you do to solve this problem? – Sanjay Panchal Jun 20 '15 at 09:43
  • I changed the format of the file and utilized bcp to get around this. This is one of the huge flaws to SSIS. BIDS does not fully support a proper CSV format as they state they do. CSV is also not the best file format for data transfer. I highly suggest using a more data integrity save format or you can use the bcp command and create a format file. This will work if you have a header row: specify your delimter to be '","' and your row delimiter to be '\r\n"'. – eddiecubed Jun 20 '15 at 16:01
  • or '\n"' if you're using linux end of line for whatever reason. I know it is a hack, and is why my first suggestion is to go a different route with the format, but it does work. You can also utilize the data transformations to remove the quotes from each column within your data flow. I have never done that, but just thinking about it now. GoodLuck. – eddiecubed Jun 20 '15 at 16:03
  • Thanks for your efforts. I have solved the problem and added as an answer. But thank you very much for your efforts. – Sanjay Panchal Jun 22 '15 at 09:39