0

I need to get data from table and write it into flat file line-by-line.

Example File 1:

Alice   ID  ST-01
Alice Gender    F
Alice   Subject English 69
Alice   Grade   C
Alice   Subject Mathematics 85
Alice   Grade   A
Alice   Subject Biology 44
Alice   Grade   D
Alice   Subject Geography   91
Alice   Grade   A
Alice TotalSubject  4

Example File 2:

Mandy   ID  ST-02
Mandy Gender    F
Mandy   Subject English 90
Mandy   Grade   A
Mandy   Subject Mathematics 63
Mandy   Grade   C
Mandy   Subject Geography   82
Mandy   Grade   A
Mandy TotalSubject  3

Example File 3:

John    ID  ST-03
John Gender M
John    Subject English 71
John    Grade   B
John TotalSubject   1

So from the example you may see that the value of TotalSubject affecting how many rows of Subject and Grade shown in file, and all file only have ONE row of ID/ Gender/ TotalSubject.

My question is, how can I use ForEach Loop Container in SSIS to get data from table and write it into flat file line-by-line?

  • Using sql 'SELECT ... UNION ALL ...' are not in my consideration, because they need to have same length of column, and I do not want my file written have empty column, it should only show the exact column that it have for each line.

Please do suggest me for solution, or other idea using ScriptTask. Thanks in advance.

mikiyaaaaa
  • 1
  • 1
  • To restate: you need to generate a fixed width output file with a mixed record type. It will have a 2 line header, content and then a trailer/summary line. What is the source table(s) structure? Is the use of a Foreach Loop container a requirement or your best guess as to the pieces required to solve the problem? – billinkc May 15 '23 at 16:05
  • The source table is actually a dump table, all recordType are in the same dump table. Foreach Loop container is not required but I think it is a good solution. – mikiyaaaaa May 16 '23 at 03:01

0 Answers0