0

I have a ssis package which gets data from sql and dumps to CSV file. There are half a million of rows and almost 100 columns and when i dump the data to Flat file destination, some of the column values gets concatenated to the next column value as it has same text deliminator as flat file connection.

For eg if the source data is like

ID    NAME    HEIGHT    ADDRESS  AGE
01    DEB      5.6"      xyz"9    29

Destination is coming like

ID    NAME       HEIGHT         ADDRESS  AGE
01    DEB      5.6",xyz"9          29

The age comes to address column in csv file.

How to avoid it as i am not sure with how many records the issue might be?

  • Text del is "
  • Column del is ,
James Z
  • 12,209
  • 10
  • 24
  • 44
TheSacredKiller
  • 121
  • 3
  • 8
  • 3
    Seems like you've defined double quotes as your text qualifier when it shouldn't be. – Thom A May 12 '22 at 12:07
  • Why do you say column delimiter is `,` when in your example it is not? Please include actual example. – James Z May 12 '22 at 14:26
  • *.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. The most reliable format for data feeds is XML enforced by XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee data format/shape, and enforce data quality. – Yitzhak Khabinsky May 12 '22 at 15:15
  • @YitzhakKhabinsky - do u have an example on how to accomplish XML enforced by XSD using ssis (any video or link to tutorial) – TheSacredKiller May 12 '22 at 16:32
  • 1
    https://www.sqlshack.com/import-xml-documents-into-sql-server-tables-using-ssis-packages/ – Yitzhak Khabinsky May 12 '22 at 17:13
  • 1
    https://learn.microsoft.com/en-us/sql/integration-services/control-flow/validate-xml-with-the-xml-task?view=sql-server-ver15 – Yitzhak Khabinsky May 12 '22 at 17:14
  • 1
    https://www.youtube.com/watch?v=NEQbDdC4Hw0 – Yitzhak Khabinsky May 12 '22 at 17:18

0 Answers0