READ INSIDE A FLAT FILE

• by-my-self

  SSC Rookie

  Points: 25

  More actions

  November 13, 2011 at 6:01 am

  #148823

  Hi ALL,

  I AM NEW IN SQL SERVER INTEGRATION SERVICES, I WOULD LIKE DOING SOME TRANSFORMATION INSIDE A FLAT FILE DELIMITED BY TABULATION BUT THERE'S NO HEADER COLUMN , MY FILE IS STRUCTURED LIKE THIS:

  E7001 02/09/2011 Ticket n° :2655

  AAA 1 200 0 DGN1 10 PN

  BBB 1 160 0 DGN2 10 NN

  E7002 01/10/2011 Ticket n° :2869

  CCC 2 65 0 DGN3 12 SS

  DDD 1 75 0 DGN4 12 PN

  AAA 1 39 0 DGN1 12 PN

  BBB 50 0 DGN2 12 SS

  I WOULD LIKE DOING TRANSFORMATION IN A OUTPUT FILE

  COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 FLAGS

  7001 02/09/2011 2655 AAA 1 200 0 DGN1 10 PN 1

  7001 02/09/2011 2655 BBB -1 160 0 DGN2 10 NN 0

  7002 01/10/2011 2869 CCC 2 65 0 DGN3 12 SS 1

  7002 01/10/2011 2869 DDD 1 75 0 DGN4 12 PN 1

  7002 01/10/2011 2869 AAA -1 39 0 DGN1 12 PN 0

  7002 01/10/2011 2869 BBB 3 50 0 DGN2 12 SS 1

  THANKS IN ADVANCE

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  November 14, 2011 at 12:55 am

  #1406945

  Your caps lock is broken.

  Since the number of columns varies per row and since SSIS can't handle that, you have 3 options:

  * read the file using .NET

  * read the file using a flat file connection manager and read everything as a single row. Parse the file using a .NET script component.

  * get the new version of SQL Server - SQL Server 2012 - as SSIS in that version can handle this type of input files more easily

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP