Dynamically write to a delimited file using a script task in SSIS

Posted on 2011-04-27
Last Modified: 2012-08-13

I'm trying to use the script task to log the error from the package into a delimated text or CSV.

my problem is it's over 100 packages and it's a nightmare to customize a flat file destination and create all the columns and map it for all of them, and all the packages has diferent input columns.

So what I need is to dump all the (input0) into a delimted text file, with minimal interaction.

I only know how to do it by row but not by the whole input.


here's my start lines:

Dim TextFile As New StreamWriter(Variables.ErrorFileDir.ToString() & Variables.PackageName.ToString() & Variables.JobID.ToString() & "-Test2.txt")

        Dim delim As String = ", "
TextFile.WriteLine(Row.code.ToString() + delim)


Question by:huslayer
    LVL 21

    Expert Comment

    by:Alpesh Patel

    Create whole row for log.
    Open existing log file as Text Stream and write one line using WriteLine function.
    Close it
    LVL 21

    Assisted Solution

    Patel, what you said is exactly what I';ve in the code, can you tell me what's wrong on my code or what could be the right code?
    LVL 21

    Accepted Solution

    I got it....

    using System;
    using System.Data;
    using System.IO;
    using Microsoft.SqlServer.Dts.Pipeline;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    public class ScriptMain : UserComponent
      public override void PreExecute()
      public override void PostExecute()
      public override void Input0_ProcessInputRow(Input0Buffer Row)
      public override void ProcessInput(int InputID, PipelineBuffer Buffer)
        System.IO.StreamWriter tw
        = System.IO.File.AppendText(Variables.ErrorFileDir + Variables.PackageName + Variables.JobID + @".txt");
        while (Buffer.NextRow())
          for (int columnIndex = 0;
           columnIndex < Buffer.ColumnCount;
            tw.Write(Buffer[columnIndex] + " | ");
        base.ProcessInput(InputID, Buffer);

    Open in new window

    LVL 21

    Author Closing Comment

    nobody gave me an answer!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now