none
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/
    Thursday, April 28, 2011 2:20 PM

Answers

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

     


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by Jason Yousef Saturday, April 30, 2011 12:43 AM
    Friday, April 29, 2011 9:08 PM
  • 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/
    • Marked as answer by Jason Yousef Wednesday, May 04, 2011 3:36 PM
    Wednesday, May 04, 2011 3:35 PM

All replies

  • 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
    By: TwitterButtons.com
    Thursday, April 28, 2011 2:37 PM
  • 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/
    Thursday, April 28, 2011 2:57 PM
  • Hi Jason,

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


    My Blog    |    Ask Me
    Thursday, April 28, 2011 3:02 PM
  • 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/
    Thursday, April 28, 2011 3:05 PM
  • 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
    By: TwitterButtons.com
    Thursday, April 28, 2011 3:20 PM
  • In the script editor (not the code editor), how many columns have you selected as "ReadOnly" or "ReadWrite"?


    Todd McDermid's Blog Talk to me now on
    Thursday, April 28, 2011 4:15 PM
  • Hi Todd,

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

    Thanks for helping.


    MCITP - BI 2008 http://asqlb.blogspot.com/
    Thursday, April 28, 2011 4:40 PM
  • Did that give you more than the one column in the collection?  Did that solve the issue?
    Todd McDermid's Blog Talk to me now on
    Thursday, April 28, 2011 5:07 PM
  • Did that give you more than the one column in the collection?  Did that solve the issue?
    Todd McDermid's Blog Talk to me now on


    Nope, that what I was doing since the beginning!


    MCITP - BI 2008 http://asqlb.blogspot.com/
    Thursday, April 28, 2011 5:13 PM
  • 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);
      }
    
    

    Todd McDermid's Blog Talk to me now on
    Thursday, April 28, 2011 8:50 PM
  • 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/
    Friday, April 29, 2011 1:51 AM
  • 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.
    Todd McDermid's Blog Talk to me now on
    Friday, April 29, 2011 7:34 AM
  • 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.
    Todd McDermid's Blog 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();
          }
    
    
        }
    
      }
    }

     

    Friday, April 29, 2011 1:41 PM
  • 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.


    Todd McDermid's Blog Talk to me now on
    Friday, April 29, 2011 4:53 PM
  • 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.


    Todd McDermid's Blog 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/
    Friday, April 29, 2011 6:24 PM
  • 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();
    }
    

     


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by Jason Yousef Saturday, April 30, 2011 12:43 AM
    Friday, April 29, 2011 9:08 PM
  • That's it................you got it.

    Thank you soooooo much.


    MCITP - BI 2008 http://asqlb.blogspot.com/
    Saturday, April 30, 2011 12:43 AM
  • 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/
    • Marked as answer by Jason Yousef Wednesday, May 04, 2011 3:36 PM
    Wednesday, May 04, 2011 3:35 PM