[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to add header and trailer to text file using ssis

Posted on 2009-02-23
Medium Priority
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
  • 2
LVL 17

Expert Comment

ID: 23730275
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)

Author Comment

ID: 23735364

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

HoggZilla earned 2000 total points
ID: 23735523
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

ID: 23791140
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 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