SSIS script task component to write Input0Buffer to a text file

Question

Hello, I'm trying to dump all input0Buffer to a txt file, to end my struggle with making the flat destination re-usable as i've over 100 package and each structure and columns are different.

my start lines:

Dim TextFile As New StreamWriter("C:\Test2.txt") Dim delim As String = ", "

TextFile.WriteLine(Row.code.ToString() & delim)

    TextFile.Flush() 
 
    TextFile.Close()  

can anyone help me please?

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 1

Hello Jason,

can we back up a tad to see what you are trying to accomplish? What re-usability are you trying to build?

 

---

Arthur My Blog

Answer 2

Hi and thank you for stepping in,

I'm redirecting the error rows to a flat file, so it's a nightmare to set that manually in every package, so I wanna write the whole input without specifieng Row.Name, all of them into text file.

 I'm up to the point that i'm getting only one column!! it's driving me crazy!!

 

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Reflection
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Pipeline


<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
  Inherits UserComponent

  Dim textWriter As StreamWriter
  Private inputBuffer As PipelineBuffer

  Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
    inputBuffer = Buffer
    MyBase.ProcessInput(InputID, Buffer)
  End Sub

  Public Overrides Sub PreExecute()
    MyBase.PreExecute()
    textWriter = New StreamWriter( "c:\-Test4.txt", True)
  End Sub

  Public Overrides Sub PostExecute()
    MyBase.PostExecute()
    textWriter.Close()
    ''
  End Sub

  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim delim As String = ", "

    Dim RowCount As Integer = 0

    For RowCount = 0 To inputBuffer.ColumnCount = -1
      If inputBuffer.Item(RowCount).ToString() = "" Then
        inputBuffer.Item(RowCount) = String.Empty
      End If
    Next

    textWriter.WriteLine(inputBuffer.Item(RowCount).ToString() & delim)

  End Sub
  
End Class

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 3

Hi Jason,

In case the metadata is not fixed this approach will not help you.

---

My Blog    |    Ask Me

Answer 4

Hi Jason,

In case the metadata is not fixed this approach will not help you.

---

My Blog    |    Ask Me

Hi,

You mean Fixed Width?  If yes,   no my Data is not fixed width !!

so what would be a better approach?

Thanks 

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 5

Got you,

I do not have the time to experiment but I think you need to iterate through the columns similar to what is shown here: http://agilebi.com/jwelch/2007/10/21/address-columns-generically-in-a-script-task/

and the resulting string needs to be dumped to the file.

E.g.

Dim sToFile As String
For counter = 0 To inputBuffer.ColumnCount – 1
	sToFile = inputBuffer.Item(counter) 
	textWriter.WriteLine(sToFile & delim)
End If

Another possibly helpful post:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/302b1c34-0198-46b1-a748-8a5a35d5b823/

---

Arthur My Blog

Answer 6

In the script editor (not the code editor), how many columns have you selected as "ReadOnly" or "ReadWrite"?

---

Talk to me now on

Answer 7

Hi Todd,

I selected all as "ReadOnly"  , around 32 columns in that specific package.

Thanks for helping.

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 8

Did that give you more than the one column in the collection?  Did that solve the issue?

---

Talk to me now on

Answer 9

Did that give you more than the one column in the collection?  Did that solve the issue?

---

Talk to me now on

Nope, that what I was doing since the beginning!

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 10

OK - here's all the code you need to get started, please post back with any questions:

  public override void ProcessInput(int InputID, PipelineBuffer Buffer)
  {
    bool fireAgain = true;
    ComponentMetaData.FireInformation(0, "", Buffer.ColumnCount.ToString() + " columns", "", 0, ref fireAgain);

    while (Buffer.NextRow())
    {
      for (int columnIndex = 0; columnIndex < Buffer.ColumnCount; columnIndex++)
      {
        BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);
        string columnData = null;
        switch (columnInfo.DataType)
        {
          case DataType.DT_BOOL:
            columnData = Buffer.GetBoolean(columnIndex).ToString();
            break;

          case DataType.DT_WSTR:
            columnData += Buffer.GetString(columnIndex);
            break;

          default:
            columnData = "";
            break;
        }
        ComponentMetaData.FireInformation(0, "", columnData, "", 0, ref fireAgain);
      }
    }
    base.ProcessInput(InputID, Buffer);
  }

---

Talk to me now on

Answer 11

Todd,

Thank you so much for time and effort to get that together, sorry to be a pain again but you know that  i need to output to a text file, so where should I place the Text writer and what to use in the output?  and I guess that's it.

 

Thanks

 

 

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 12

Opn the file for writing at the top of the method.  Instead of the calls to FireInformation, put your calls to write to the output file.  Close the file after the loop.

---

Talk to me now on

Answer 13

Opn the file for writing at the top of the method.  Instead of the calls to FireInformation, put your calls to write to the output file.  Close the file after the loop.

---

Talk to me now on

Thanks again Todd, but I still can't figure it out by my own, i'm so beginner when it comes to .net, i'm getting an empty file or error!!  tried many variations and code combinations but I really don't know what i'm doing here !!

here's my full code. Hope you still can help me.

 and thanks again for your time.

 

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)
  {
    /*
     Add your code here
    */
  }
  public override void ProcessInput(int InputID, PipelineBuffer Buffer)
  {
    TextWriter tw = new StreamWriter("C:\\Test.txt");
    bool fireAgain = true;
    ComponentMetaData.FireInformation(0, "", Buffer.ColumnCount.ToString() + " columns", "", 0, ref fireAgain);

    while (Buffer.NextRow())
    {
      for (int columnIndex = 0; columnIndex < Buffer.ColumnCount; columnIndex++)
      {
        BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);
        string columnData = null;
        switch (columnInfo.DataType)
        {
          case DataType.DT_BOOL:
            columnData = Buffer.GetBoolean(columnIndex).ToString();
            break;

          case DataType.DT_WSTR:
            columnData += Buffer.GetString(columnIndex);
            break;

          default:
            columnData = "";
            break;
        }
        ComponentMetaData.FireInformation(0, "", columnData, "", 0, ref fireAgain);

        foreach (IDTSInputColumn100 column in this.ComponentMetaData.InputCollection[0].InputColumnCollection) 
        {
          tw.Write(column.ToString());
        }
        base.ProcessInput(InputID, Buffer);
        tw.Close();
      }


    }

  }
}

 

Answer 14

Your code's looking pretty OK.

You should probably remove the switch statement entirely, since you're not using it at all.

Second, the reason you're probably getting an empty file is because the StreamWriter constructor you're using tries to overwrite the file every time its called, and it's going to get called more than once, and it's probably going to get called last with a buffer with no records in it.  You probably want to specify the second argument as "true" or use the File.AppendText method which does that for you.

---

Talk to me now on

Answer 15

Your code's looking pretty OK.

You should probably remove the switch statement entirely, since you're not using it at all.

Second, the reason you're probably getting an empty file is because the StreamWriter constructor you're using tries to overwrite the file every time its called, and it's going to get called more than once, and it's probably going to get called last with a buffer with no records in it.  You probably want to specify the second argument as "true" or use the File.AppendText method which does that for you.

---

Talk to me now on

Thanks for the advise but no hope!! still empty...

using System;
 Hope you can take a second look and tell me what i'm doing wrong !

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 = new System.IO.StreamWriter("C:\\test.txt", true);
    
    StreamWriter tw = File.AppendText("d:\\Test.txt") ;     //didn't help too !!

    //TextWriter tw = new StreamWriter(@"C:\Test.txt", true);  //didn't work !!

    bool fireAgain = true;
    ComponentMetaData.FireInformation(0, "", Buffer.ColumnCount.ToString() + " columns", "", 0, ref fireAgain);

    while (Buffer.NextRow())
    {
      for (int columnIndex = 0; columnIndex < Buffer.ColumnCount; columnIndex++)
      {
        BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);
        string columnData = null;

        //switch (columnInfo.DataType)
        //{
        //  case DataType.DT_BOOL:
        //    columnData = Buffer.GetBoolean(columnIndex).ToString();
        //    break;

        //  case DataType.DT_WSTR:
        //    columnData += Buffer.GetString(columnIndex);
        //    break;

        //  default:
        //    columnData = "";
        //    break;
        //}

        ComponentMetaData.FireInformation(0, "", columnData, "", 0, ref fireAgain);

        foreach (IDTSInputColumn100 column in this.ComponentMetaData.InputCollection[0].InputColumnCollection) 
        {
          tw.WriteLine(columnData.ToString()); 
          
          //File.WriteAllText(@"D:\Test.txt", column.ToString());
        }
        base.ProcessInput(InputID, Buffer);
        tw.Close();
      }


    }

  }
}

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 16

OK - you're pretty messed up there.  You've got a column loop within a column loop and are closing the file and discarding the buffer after you see the first row.  Try this:

 

public override void ProcessInput(int InputID, PipelineBuffer Buffer)
{
  System.IO.StreamWriter tw 
  = System.IO.File.AppendText(@"D:\Test.txt");

  while (Buffer.NextRow())
  {
   for (int columnIndex = 0; 
    columnIndex < Buffer.ColumnCount; 
    columnIndex++)
   {
     tw.Write(Buffer[columnIndex]);
   }
   tw.WriteLine();
  }
  base.ProcessInput(InputID, Buffer);
  tw.Close();
}

 

---

Talk to me now on

Answer 17

That's it................you got it.

Thank you soooooo much.

---

MCITP - BI 2008 http://asqlb.blogspot.com/

Answer 18

That's it................you got it.

Thank you soooooo much.

---

MCITP - BI 2008 http://asqlb.blogspot.com/

This is what I ended with....For sharing with others.

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();
  }
  
}

 

---

MCITP - BI 2008 http://asqlb.blogspot.com/