Click here to Skip to main content
14,735,364 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to use an SSIS script task to transfer values from a table to a text file. The text file will be used to print checks, so it is formatted as such. I have Temporary Check table flowing to a Script Transformation component. The package completes successfully but only the last row from the database is captured in the text file. I have researched how to iterate through each row and I seem to be getting more confused. I am not sure if I have to configure the input/output in package or create variables to call out in the script. I am very new to C# so that is not helping either. Any advice/guidance would be greatly appreciated. Below is the code I have currently to achieve the required format. I need to loop through the database and apply the formatting for each row/ check to be printed.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        StreamWriter sw = new StreamWriter("c:\\test.txt");
        sw.WriteLine("XXXXXXXXXXXX" + "XXXX".PadLeft(52) + Row.CheckID
            + "\r" + "\nXXXXXXXXXXXXXX" + "XXXXXX\r".PadLeft(27)
            + "\nP. O. BOX XXXX" + "24 HOUR BANKING".PadLeft(36)
            + "XX-XXXX".PadLeft(27) + "\r"
            + "\nXXXXXXXX, XX  XXXXX" + ",".PadLeft(17) + "-------\r".PadLeft(42)
            + "\n    " + "XXXX\r".PadLeft(71)
            + "\n    ".PadRight(50) + Row.FName + " " + Row.LName + " ".PadLeft(19) + DateTime.Now.ToString("MMM dd yyyy").ToUpper()
            + "\r" + "\n "
            + "\r" + "\n " + " \r"
            + "\n ".PadRight(8) +"**XXX DOLLARS AND 00/100**" +" ".PadLeft(37)+Row.Amount
            + "\r" + "\n "
            + "\r" + "\n " + " \r"
            + "\n ".PadRight(11)+Row.VendorName1
            + "\r" + "\n ".PadRight(11)+Row.VendorName2
            + "\r" + "\n ".PadRight(11)+Row.VendorName3
            + "\r" + "\n ".PadRight(11)+Row.VendorAddress1
            + "\r" + "\n ".PadRight(11)+Row.VendorAddress2
            + "\r" + "\n ".PadRight(11)+Row.VendorCity+","+ " "+Row.VendorState+" ".PadLeft(4)+" "+Row.VendorZip
            + "\r" + "\n " + " \r"
            + "\n ".PadRight(11)+"XXXX"+Row.CheckID + "    "+"XXXXXX"+"     "+"XXXXXXXXXX"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r"
            + "\n " +"XXXX-".PadLeft(71) + Row.CheckID
            + "\r" + "\n " + " \r"
            + "\r"
            + "\n" + DateTime.Now.ToString("MM/dd/yyyy") + " ".PadLeft(2) + Row.ClientID + " ".PadLeft(3) + " ".PadLeft(3) + Row.FName + " " 
                   + Row.LName + " ".PadLeft(9) + Row.TCDesc1  + " ".PadLeft(29) + Row.Amount
            + "\r" + "\n "
            + " ".PadLeft(39) + "TCDesc2" + Row.TCDesc2
            + "\r" + "\n "
            + " ".PadLeft(39) + "TCDesc3" + Row.TCDesc3
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r" + "\n " + " \r"
            + "\r"
            + "\r"
            + "\r"
            + "\r"
            + "\r"
            + "\n" + " ".PadLeft(81) +  Row.ClientID + " ".PadLeft(9) + Row.FName + " " + Row.LName; 

             sw.Close();

        
    }


What I have tried:

I tried using an Execute SQL task and a ForEach Loop Container and it kept erroring out. Cannot recall the specific error.
Posted
Updated 7-May-17 3:46am
v2
Comments
Dave Kreskowiak 1-May-17 20:38pm
   
Well, without the error, it's pretty much guesswork to try and help you.

1 solution

The reason that only the last row from the database is written to the file, is because you are recreating the file each time you call this Input0_ProcessInputRow method. You need to create and close the file outside of the loop

Do something like this instead:
public void Input0_ProcessFile()
  {
      //
      // Put in here whatever you do to get datainput from the database
      //

      //Create the file once!
      using (var sw = new StreamWriter("c:\\temp\\test.txt"))
      {
          //Process each row in the datainput
          foreach (var row in datainput)
              Input0_ProcessInputRow(sw, row);
      }
  }
Note I've also put the file into a sub-folder. It is bad practice to store files on the root folder C:\ and in many cases you will be prevented from doing so, particularly if this script is running on the server. This may be the error you encountered.

Also note that I've changed the signature of your method to pass in the reference to the StreamWriter
public override void Input0_ProcessInputRow(StreamWriter sw, Input0Buffer Row)
{
...
Here is the MSDN Reference (there are more links in the sidebar to the page in the link). How to: Write Text to a File[^]

[Edit] As an aside consider using System.Environment.NewLine instead of all of those escape characters.
Another alternative would be to convert the data into and XML file and use an HTML Template/XSLT to transform the data into a "report" - see Creating and Populating an HTML Template (Windows CE .NET 4.2)[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900