How to add a footer to a text file in SSIS (Visual Studio 2010)

How does my SSIS job append a footer to a text file that has just been written?
PJLewis2015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi PLewis,
After your Dataflow task which creates the text file, add in a Script Task.
In this Script Task you can write C# code which opens the  text file and appends the necessary footer lines.
In C# you can use the StreamWriter object to achieve this.
PJLewis2015Author Commented:
Thanks for the quick response Barry.  Unfortunately, I barely remember my VB Script and have not used C#.

I am trying to append "EOF" to the flat file (customer requirement).  The number of records can be large.

Is there a way I could use T-SQL  to attach "EOF" as the last row in the result set?
Barry CunneyCommented:
Hi PLewis,
You can also write a Script Task in VB.Net - there is simply a flag you set when you add the Script Task to the main pane.
VB.Net to write a line to a file is very simple:

Dim FILE_NAME As String = "C:\data\datafile.txt"

Dim objWriter As New System.IO.StreamWriter( FILE_NAME, True )


objWriter.WriteLine("EOF")


objWriter.Close()

The great thing about going the Script Task approach is that it is much more extensible for the future - it leaves more doors open - you can also pass variables into the Script Task - so you could have a separate Execute SQL Task which gets the recordcount from a table and assigns this to an SSIS variable and you could pass this same variable into this Script Task and the Script Task could then write out the footer line to the file with the recordcount
objWriter.WriteLine("EOF - RowCount: " & RecordCountVariable)

If you really do not wish to go the Script Task approach right now a more fudged approach may be to have separate task(Could be a dataflow task or maybe a Execute Process Task(which does a DOS echo command to populate a file)) which outputs the required footer to a separate file. Then you could have a final Execute Process task which calls a DOS command which echoes out the footer file and appends it to the main data file populated by the data flow task
TYPE 'footer file full path' >> 'data file full path'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PJLewis2015Author Commented:
Thanks and it works.

FYI, I found a faster way:  

Let's say I use a simple SQL query:

      SELECT ID, NameF, NameL FROM Employee.

Then for giggles, I tried a UNION

      SELECT SSN, NameF, NameL FROM Employee
      UNION
      SELECT TOP 1 '' AS SSN, '' AS NameF, '' AS NameL FROM Employee
      ORDER BY SSN

It worked when I used the second SQL query above in the SSIS package!
Jim HornMicrosoft SQL Server Data DudeCommented:
I've done this in various SSIS gigs with a single data pump to a flat file with only one column, using a subquery that UNIONs all sections, and an extra column for sort:
SELECT a.detail
FROM (
   -- Header row
   SELECT 0 as sort_order, 'H' + header1 + header2 + header3 as detail
   FROM some_table
   UNION ALL
   --- Detail rows
   SELECT 1 as sort_order, 'D' + detail1 + detail2 + detail... + detail42
   FROM some_table
   UNION ALL
   --- Footer row
   SELECT 2 as sort_order, 'F' + CAST(SUM(amount) as varchar(100)) 
   FROM some_table ) a
ORDER BY a.sort_order

Open in new window

PJLewis2015Author Commented:
You aren't 'da man' - you're his big brother!

Thanks for your advice.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.