Loop through database in SSIS script transformation

Question

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.

C#

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 1-May-17 13:34pm

Member 13093390

Updated 7-May-17 2:46am

CHill60

v2

Add a Solution

Comments

Dave Kreskowiak 1-May-17 20:38pm

Well, without the error, it's pretty much guesswork to try and help you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2017-05-07T02:35:00

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:

C#

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

C#

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)[^]