Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records.

For all the records, I would like to add the Status field which has either "success" or "fail" and this gets output from script component.

I then log those output into a text file.

Problem: I'm not been able to add status for each input records since the web service call only happens on post execute.

I tried this but still doesn't work:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData 
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime

         });
 }

 public override void CreateNewOutputRows()
 {
     MessageBox.Show("Test CreateNewOutputRows");
     MessageBox.Show(listOfData.Count.ToString());
     foreach (var item in listOfData)
     {
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
     }
 }
share|improve this question
1  
Why can you not execute the web service call during the processinputrows? All the data has already gone down the pipeline once it's out of that method. You are accumulating data in your local variable but at this point it's like holding a picture of a car that just drove away-yes, that's what it looked like but it's not coming back –  billinkc Oct 23 '11 at 14:58
    
@flybyte: anything useful in my answer? –  Marian Nov 3 '11 at 19:51

2 Answers 2

Everything in a Transformation is done within Input0_ProcessInputRow The solution would essentially be

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData 
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime
         });
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
}

You may be able to perform the output steps in PostExecute, CreateNewOutputRows is not run in Transformation, only in Destination scripts.

share|improve this answer

I'm not very proficient in SSIS, but I think that you could try the following ideas:

  • go to the script component and edit it,
  • go to section Inputs and Outputs (3rd section),
  • go to Output0 - Output columns,
  • add a new column (give it a name and type, let's say Status - boolean).

Then you'd have the empty column to your output and you will need to populate it with some data for each of the input rows:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.Status = IIf(String.IsNullOrEmpty(rowValues.GetValue(1).ToString()), 0, 1)

This will help you add a Status column to your output table. Hope this is what you wanted.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.