How to add header and trailer to text file using ssis

Posted on 2009-02-23
Last Modified: 2013-11-10
I want to add header and trailer to text file using ssis.
The header and trailer must be in different rows.
I attched ssis package which I created.But i  did not get correct result.

can any one check and give right suggestions

Question by:coventri
    LVL 17

    Expert Comment

    There is some promise in using the approach in your package. A UNION might accopmplish this if you write your SQL to produce the "exact" same columns. Instead of asking SSIS to build the file lines for you, you would need to create them in your SQL.
    For example:
    select  1 as rownum, col1 + col2 + cast(sum(col3) as varchar(30)) as fileline from ... (header row)
    union all
    select 2 as rownum, col3 + col5 + col6 + col9 + col19 + col33 as fileline from ... (detail rows)
    union all
    select 3 as rownum, .... as fileline from ... (footer / trailer)
    LVL 1

    Author Comment


    I am using oledb source in ssis(sql command as data acess mode) to prepare data .How can  I pass
    col 2 as variable in header row

    LVL 17

    Accepted Solution

    You can't pass the variable into the Source SQL, however you can add the variable to the data in a Derived Column task. In the SQL, put some text that identifies where the variable should go and then in the derived column do a replace on that "text" with the value from the variable.
    LVL 30

    Expert Comment

    You could export a seperate header.txt and trailer.txt file, then use a DOS COPY command to append them all together.
    COPY Header.TXT + Data.TXT + Trailer.TXT FinalFile.TXT

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now