2

I have a SSIS package where1 record (hard coded) flow through.

enter image description here

I have variable in DFT scope.

enter image description here

I assign value to variable using Row Count Transaformation.

enter image description here

The value should be 1 i verify it by using script component.

 public override void PostExecute()
  {
    System.Windows.Forms.MessageBox.Show(ReadWriteVariables[0].Value.ToString());
    base.PostExecute();
    /*
      Add your code here for postprocessing or remove if not needed
      You can set read/write variables here, for example:
      Variables.MyIntVar = 100
    */
  }

I look for zero condition through condition in Conditional split transformation.

enter image description here

Strangely it satisfies equal to zero condition whrease I think it should have value 1. Even Messagebox through script component shows value 1.

enter image description here

what could be the reason? Are value in varible realize only towards end of DFT or Conditional Split has some problem reading correct value or something else which i am not able to think up?

Michael
  • 3,982
  • 4
  • 30
  • 46
Pritesh
  • 1,938
  • 7
  • 32
  • 46

2 Answers2

4

The value for variable being assigned inside a data flow task can't be used in the split transformation or later in the Data Flow task . The values generally get populated once DFT gets completed .

Variable values does not update during the execution of Data Flow task

Even though you are able to see value 1 or set some other value to Variable from script transformation in post or pre execution events ,these values gets effected only after the execution of DFT

Hence the updated value can be used in precedence constraint or other tasks in control flow .

Read this article .

praveen
  • 12,083
  • 1
  • 41
  • 49
  • thanks! I have a huge number of ETL (DFT)s in my package. I wish to do some forward filling (basicaly take a different flow) when I get zero records from source. how should i try it? My conditional split/Row Count approach has fallen flat. – Pritesh Mar 13 '13 at 13:47
  • 2
    Although not particularly smart or efficient, you could just run a SELECT COUNT(*) FROM Blah beforehand and save that value to a variable. It requires duplication of your query and and additional pass at the data. – Nick.Mc Mar 13 '13 at 23:04
0

Alternatively you can use RANK Function as one of the columns, latter use Conditional split with max function to get the number of rows selected (in directly row count). Next you can use Copy column and remove RANK column before inserting into final destination. Hope this helps!

Anant Rai
  • 1