Adding headers to flat files(zz)

原文地址：http://sqljunkies.com/WebLog/knight_reign/archive/2004/12/06/5404.aspx
So, you want to record some information in a flat file about when it was created, what machine it was created on etc.?

The flat file destination adapter has a field in the UI that allows you to enter some text that will be included in the header, but then it's static and won't change from package execution to package execution. There's a lot of information stored in the variables and other locations in the package. It would be nice to be able to place that information into the header.

You can this information into the header and you can build the header to be as complicated as you like.
Here's how:

Create a variable for specifying the location of the destination flat file.
I called it FileLocation. (You can also retrieve the Flat File Connection Manager and get the connection string, which is more robust)
Drop a script task onto the designer.
Specify the FileLocation and System::StartTime variables on the ReadOnlyVariables property in the property grid.
Put the following code into it:
Public Class ScriptMain

Public Sub Main()

Dim time As Variable = Dts.Variables("System::StartTime")

Dim fileLocation As Variable = Dts.Variables("FileLocation")

Dim file As New System.IO.StreamWriter(fileLocation.Value().ToString())

file.WriteLine(time.Value.ToString())

file.Close()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Now, drop a pipeline on the designer surface and build it ensuring that the destination flat file adapter has "Overwrite data in the file" _unchecked_.

Drag a success precedence constraint from the script task to the pipeline.

This will put a nice header in the file and allow the pipeline to write all the data after it.

Thanks,
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden