3

When I try to add a column and set the ColumnDelimiter in the Flat File Connection Manager Editor, I get the error:

The row delimiter cannot be the same as the column delimiter.

How can I specify the delimiter?

Hadi
  • 36,233
  • 13
  • 65
  • 124
live-love
  • 48,840
  • 22
  • 240
  • 204

3 Answers3

1

Check your source file, it might not have the column you are trying to add.

Click on Browse, and reload the file with the column.

enter image description here

Second solution:

The format specified in the Flat File Connection Manager Editor on the first page should be delimited only.

Change your format to something else, than change it back to "Delimited" and then go to the "Columns" section, the column delimiter dropdown should be enabled.

live-love
  • 48,840
  • 22
  • 240
  • 204
  • This can be considered as a workaround, while it can be done without changing the whole flat file configuration. – Hadi Dec 04 '19 at 17:16
1

Yes this error is obvious , please select from flat file connection manager row delimiter as {CR}{LF} and any of the highlighter for column delimiter , you will not get the error.

Generally we use {CR}{LF} as to read the end of line, please check the definition below for {CR}{LF}.

End of Line characters include CR or LF. Windows uses both {CR}{LF} at the end of a line, whereas Unix uses only a LF.

  • CR = Carriage Return
  • LF = Line Feed

please follow the snapshot and choose the highlighted delimiter as column delimiter to avoid the error in your package.

enter image description here

Ajeet Verma
  • 1,021
  • 1
  • 7
  • 25
  • I agree with that, but if the main error is that when the OP is trying to **When I try to add a column and set the ColumnDelimiter in the Flat File Connection Manager Editor** as he mentioned, he was receiving this error and he was unable to add the column and to get back to other tabs, then this solution will not solve the issue. – Hadi Dec 04 '19 at 17:05
  • If you check the DTS assemblies which are used to automate the SSIS packages creation you will see that changing the delimiters from the columns tab will loop over each column and change the columndelimiter in the same way i described in my answer – Hadi Dec 04 '19 at 17:07
  • I updated my answer to reproduce the error, you can check it for more information – Hadi Dec 04 '19 at 17:13
0

Assuming that you are trying to add a new column from the Advanced tab in the Flat File connection manager, you must note that the ColumnDelimiter property for each column is were you set the column delimiter except the last column where this property is used to set the row delimiter.

Getting back to the error:

The row delimiter cannot be the same as the column delimiter.

You must check that the last column delimiter is different from the previous columns.

Update 1 - Experiments

I added a new flat file conneciton manager, the in the Advanced Tab I added two columns, from the screenshots below, you can check that the first column delimiter is , while the second is {CR}{LF}.

enter image description here

enter image description here

If you try to change the second delimiter to , you will receive the following error and the property cannot be changed:

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • May I know the reason why you are selecting CRLF in column delimiter in second screenshot ,can you imagined a flat file with column delimit as {CR}{LF}. – Ajeet Verma Dec 04 '19 at 17:19
  • @Ajeet as i mentioned, the last column's Columndelimiter property is the row delimiter – Hadi Dec 04 '19 at 17:23