1

I've got an SSIS package (SQL 2014) that loads data from a table into a flat file. The file has 5 columns, however there is one row in my dataset that is used by the system for duplicate checking, and its required to have 3 columns, instead of 5.

How my file looks like now:
ID|Desc|UDF1|UDF2|UDF3
DUPECHECK|SaysSomethingIrellevant|||
ID1|Desc1|||
ID2|Desc2|||

How I want my file to look:
ID|Desc|UDF1|UDF2|UDF3
DUPECHECK|SaysSomethingIrellevant|
ID1|Desc1|||
ID2|Desc2|||

You can see how the second row of the file should have a different number of columns than the rest of the rows. How am I able to do this?

Pops
  • 468
  • 2
  • 15
  • 1
    I might be mistaken, but as far as I know, this is not possible at all! Should be the same logic as with tables: Each row of a table has the same number of columns. Anyways, if you need this specific structure, I would suggest creating a Script Component as destination and write the flatfile from there. – Tyron78 Nov 14 '16 at 16:46
  • I was hoping there was a fancy workaround where I didn't need to use a Script task, but thanks for the input. – Pops Nov 14 '16 at 16:52

1 Answers1

2

You cannot do it. The only way I did it(I had to write a file with a header row and footer row which had different number of columns) is to either write everything (all the columns) to a row with a single column or to write three different txt files and then combine the the three file using a bat file.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • Thanks, I was able to do it in SSIS using a combination of both of your suggestions. I made 3 streams (one for the header row, one for the DUPLICATE row, one for the rest of the entries), then I added the pipes in a derived column, then I used a union all to combine into one column, which is the only column in my flat file. – Pops Nov 14 '16 at 17:50