SSIS text file output

Wonderwall
Wonderwall used Ask the Experts™
on
Aloha I am writing out a text file from a table and that is working fine. I need to add a header and trailer that are in a different format than the rest of the file. How do I approach this best? can I write to the file in the first step run the data pull from the table and then write out the trailer in a third step or is there another way? mahalo  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
I suggest to append header and trailer in a single step right after data flow task.
the reason is:
if you put header at first and then go to data flow, flat file connection manager will cause validation error, because it can not understand structure of columns in flat file.
so do this with a script task after the data flow task,
you can use this script to add header and trailer:
System.IO.StreamReader sr=new System.IO.StreamReader(@"C:\test.txt");
string data=sr.ReadAllLines();
sr.Close();
System.IO.StreamWriter sw=new System.IO.StreamWriter(@"C:\test.txt",false);
data="put header here"+"\r\n"+data+"\r\n"+"put trailer here";
sw.Write(data);
sw.Flush();
sw.Close();

Author

Commented:
Hi, I haven't used the script task before not sure where to put the code and set this up. I pasted in your code thru design script but showing an error on the task when I back out. mahalo  
Reza RadConsultant, Trainer

Commented:
you should add a script task
then double click on it
then set language as visual c#
then click on edit script
then put the code inside Main() method
right at the place where comment shows "put your code here..."
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Thx thats where i went wrong but I am only given an option of visual basic .net I can't enter anything else in. Is there a default I need to turn off somewhere?
Reza RadConsultant, Trainer

Commented:
seems that you are using SSIS 2005, because ssis 2005 has no Visual C# language option in script task.
never mind, I changed the code to vb.net, now use this code :

dim sr as new System.IO.StreamReader("C:\test.txt")
dim data as string
data=sr.ReadAllLines()
sr.Close()
dim sw as new System.IO.StreamWriter("C:\test.txt",false)
data="put header here"+"\r\n"+data+"\r\n"+"put trailer here"
sw.Write(data)
sw.Flush()
sw.Close()



Author

Commented:
hey thx a bunch I am running but got this error

Error 30456: 'ReadAllLines' is not a member of 'System.IO.StreamReader'.
Line 23   Column 16 through 30 Any ideas/
Consultant, Trainer
Commented:
my mistake!
I wrote that code on the fly, right here without syntax check in .net, so try this one now instead the previous line:
data=sr.ReadToEnd()

Author

Commented:
Awesome !!! Can't thank you enuf...  

Author

Commented:
Reza is the best !!!
Reza RadConsultant, Trainer

Commented:
you're welcome,
Glad to help,
Regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial