Why doesn't SSIS recognize tab column delimiters while importing into a database?

Question

I am trying to use an SSIS package to transfer data from a .dat flat file to a SQL Database table. I have a dat file that looks like this:

Booth 1 Weekly  Status
Status  Date    Time    Operator    NESHAP  HEPA    Alarm
Initial Reading 10/3/2011   7:42 AM Ken Forsberg    0.00    0.00    No
System Reading  11/12/2011  3:10 AM System Log  0.24    2.01    No

No Login    11/12/2011  7:00 AM     0.24    2.03    Yes
No Login    11/12/2011  3:00 PM     0.24    2.03    Yes
No Login    11/13/2011  7:00 AM     0.24    2.04    Yes
No Login    11/13/2011  3:00 PM     0.24    2.02    Yes
No Login    11/14/2011  7:00 AM     0.24    2.06    Yes
No Login    11/14/2011  3:00 PM     0.24    2.05    Yes
No Login    11/15/2011  7:00 AM     0.24    2.06    Yes
No Login    11/15/2011  3:00 PM     0.24    2.04    Yes
No Login    11/16/2011  7:00 AM     0.24    2.05    Yes
No Login    11/16/2011  3:00 PM     0.24    2.05    Yes
No Login    11/17/2011  7:00 AM     0.24    2.07    Yes
No Login    11/17/2011  3:00 PM     0.25    2.10    Yes
No Login    11/18/2011  7:00 AM     0.25    2.09    Yes

But when I run the Flat File Connection Manager Editor

I have this for general:

Locale English(United States)
Code page 1252 (ANSI - Latin I)    
Format: Delimited    
Text qualifier: none    
Header row delimiter: {CR}{LF}    
Header rows to skip: 0    
Column names in the first data row: checked

And for the Columns I have:

Row delimiter: {CR}{LF}    
Column delimiter: Tab {t}

But in the Preview window it only shows one column

StatusDateTimeOperatorNESHAPHEPAAlarm    
InitialReading10/3/20117:42 AMKen Forsberg0.000.00No                
System Reading11/12/20113:10 AMSystem Log0.242.01No    
NoLogin11/12/20117:00 AM0.242.03Yes    
NoLogin11/12/20113:00 PM0.242.03Yes    
...

I have the row delimiter as Carriage Return and Line Feed and the Column delimiter is Tab but as you can see the tab is not being recognized.

I did open the .dat file in Notepad++ to confirm that they are delimited by Tab and they are.

What am I doing wrong?

Answer 1

Here are the issues:

1. The file has a irrelevant row in the first row. When I say irrelevant, I mean that it doesn't match well with the rest of the file.

2. The header row is actually in the second row of the file and not in the first row.

I took the data in the question and put it in a .dat file. Make sure that your file looks exactly as shown below when you open in Notepad++. The arrows indicate the tabs. To view special characters in Notepad++, click on View --> Show Symbol --> Show All Characters

When you configure the Flat File Connection manager, enter the value 2 in the field Header rows to skip. Also, do not check the checkbox Column names in the first data row because the column names are not in the first row but they are in the second row.

When you switch to the the Columns section, the columns should correctly display the data provided your file actually matches with what is shown in the first screenshot. Also, SSIS will automatically detect the Row and Column delimiters. However, the rows will be named Column 0, Column 1 etc because we skipped 2 rows.

You can click on the Advanced tab and rename the column names according to your preference.

Also, the data should be displayed correctly in the Preview section.

Hope that helps.

Answer 2

Which version of SQL Server do you have?

My client has SQl Server 2008R2 SP2. I have not narrowed it down to a version.

I have found that even though Flat File Connection Manager shows that a 'Row Delimeter' is set, the XML code does not have the hexadecimal in the code.

My example is using the Row Delimiter LF

Note the ASCII value is the 4 characters and in this case 000A. The other characters are also required. Prefix is _x, Suffix is _

To modify the XML code open the SSIS project and right click on the package and select 'View Code'. It should be on line 41. For other Row Delimiters look up the ASCII table.

You can do a Search and Replace on this string. Use replace all and Look in 'Current Project'

Find What: "RowDelimiter" xml:space="preserve">x000A

Answer 3

I think that you need to skip at least one of the header rows since Booth 1 Weekly Status is on the first row, no the header fields and you have instructed SSIS to get the header fields from the first row.

Answer 4

Just to test, open the .dat file in excel and see if you get the desired results.

As @competent_tech mentioned you might want to take care of the first row :-)