csv file with dynamic field counts

• peacesells

  SSCrazy

  Points: 2882

  More actions

  December 18, 2020 at 5:18 am

  #3821884

  i have a csv file with dynamic field counts that i need to import into a table using data flow task in ssis. The expected field count in the file is 10.Need to skip any rows where the field count is anything other than 10 . The file has " as text qualifier and  need  ignore the delimiter (,)  within the qualifier. What would be the best way to do this using ssis? Thanks.

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  December 18, 2020 at 9:38 am

  #3821941

  SSIS does not handle this scenario very well without code. The way I would do it is as follows:

  • Set up a dataflow to read every row of the source file as a single column
  • Using a Script Component to parse out the actual columns and discard (or redirect) those rows with incorrect column counts. The output of the Script Component will be the columns you are interested in.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel