Append header to data

I have to append a header to data in a text file

I have to make an output file   ouput.txt  by combining  2 sets of Data.  The files are not related.
 
The Header information does not change(static)
Data 1

Header.txt
ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING    

Data 2 is from table and changes every night

Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000


I am not sure how to combine the header (header.txt) with the details in Table B to get the desired output.txt Text file   ---as below

ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING      
Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000

I prefer to do this in SSIS.  Any other alternative is good.  I just need the output urgently
Nana HemaaAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I've done this multiple times in the past, and I've pulled it off with a separate column that identifies the headers-detail-footer (sort_order), then sort on it but don't select it in the source of the data flow that creates your text file...
-- Header row
INSERT INTO YourTable (sort_order, column_type, detail) 
VALUES (1, 'H', 'here it comes...') 

-- Detail row
INSERT INTO YourTable (sort_order, column_type, detail) 
SELECT 2, 'D', big_honking_column
FROM some_table

-- Footer row
INSERT INTO YourTable (sort_order, column_type, detail) 
VALUES (3, 'F', 'there it was') 

-- Now use this as the source of the pump to the text file..
SELECT column_type, detail
FROM YourTable
ORDER BY sort_order

Open in new window

0
 
plusone3055Commented:
just use a union

SELECT      'header1' AS header1
                  ,'header2' AS header2
                  ,'header3' AS header3
                  ,'header4' AS header4
                  ,'header5' AS header5
                 ,'header6' AS header6
UNION ALL
SELECT column1
             ,column2
            ,column3
           ,column4
          ,column5
         ,column6
FROM TABLE
0
 
Nana HemaaAuthor Commented:
Tried union before but got an error because the number of columns in the header does not match the details.  20 columns in the header and the details just about 5 or 6 columns
0
 
plusone3055Commented:
Abonsu,

then to fix that you create 20 headers..  label the ones that you need and label 'noHeader" for hte ones you don't so they match up :)
0
 
Nana HemaaAuthor Commented:
Jim--did you use SSIS to accomplish your output--my columns do not match as nicely as yours above--

The header information is now in a text file with 1 line --header.txt
ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING    

Details in table B
Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000


I have created a flat file source to point to header.txt
An Ole Db Source to Table B
A flat file destination to point to Output.txt

Now what I am having issues with is how to get the header information and details information to Output.txt since the coulmns do not match
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.