Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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

Hello,

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.

Thanks


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)


        TextFile.Flush()

        TextFile.Close()
0
Jason Yousef, MS
Asked:
Jason Yousef, MS
  • 3
2 Solutions
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Create whole row for log.
Open existing log file as Text Stream and write one line using WriteLine function.
Close it
0
 
Jason Yousef, MSSr. BI DeveloperAuthor Commented:
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?
0
 
Jason Yousef, MSSr. BI DeveloperAuthor Commented:
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;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

  public override void PreExecute()
  {
    base.PreExecute();
    
    
  }

  public override void PostExecute()
  {
    base.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;
       columnIndex++)
      {
        tw.Write(Buffer[columnIndex] + " | ");
      }
      tw.WriteLine();
      
    }
    base.ProcessInput(InputID, Buffer);
    
     tw.Close();
  }
  
}

Open in new window

0
 
Jason Yousef, MSSr. BI DeveloperAuthor Commented:
nobody gave me an answer!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now