locked
RowCount RRS feed

  • Question

  • Hi,

     want to get the number of rows i'm retrieving from a source. This count should be written as " No: of roes retrieved" + varname

    I have used OleDbSource, RowCount,Script [ To write in  a file ]. Rows is the package level variable name used in rowcount. when i do this way it always writes as 0 in the file.

    [code in Script]

    Dim sw As New StreamWriter("D:\Vijay1.txt")

    s = Variables.Rows

    sw.WriteLine(s.ToString)

    sw.close

    [/Code]

    Can anyone help on this

     

    Monday, June 26, 2006 2:55 AM

Answers

  • Can you try as follows:

    Dim sw As New StreamWriter("D:\Vijay1.txt")

    sw.WriteLine(Dts.Variables("Rows").Value.ToString())

    sw.close()

     

    Thanks,
    Loonysan

    Monday, June 26, 2006 3:45 AM
  •  ManjuVijay wrote:

    Hi,

     

    I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

     

    Thanks

    the code should be:

    s = Dts.Variables("Rows").Value

    Monday, June 26, 2006 3:49 AM
  • > If i use script task it works properly

     >why i am not able to do so in script transform component

    The script component is used within a DataFlow task. The DataFlow task "snapshots" a variable value when it begins execution and cannot modify the variable until it has completed.

    So, your row count = 0 at the beginning of execution. Your script component accesses the "snapshot" value and writes out 0.

    The RowCount component only updates the row count variable, when execution of the data flow has completed. Your script task accesses the value after this and writes out the final rowcount.

    Why does SSIS snapshot variable values? Well imagine a conditional split where the data is split on a variable value. If that could change during execution of a data flow, the behaviour of the split would be unpredictable - rows would be directed depending on whether they just happened to reach the split before or after the variable changed.

    Donald 

     

     

    Monday, June 26, 2006 4:19 PM

All replies

  • you should store the row count in an ssis variable, then retreive the value from the script...
    Monday, June 26, 2006 3:27 AM
  • Hi,

     

    I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

     

    Thanks

    Monday, June 26, 2006 3:37 AM
  • Can you try as follows:

    Dim sw As New StreamWriter("D:\Vijay1.txt")

    sw.WriteLine(Dts.Variables("Rows").Value.ToString())

    sw.close()

     

    Thanks,
    Loonysan

    Monday, June 26, 2006 3:45 AM
  •  ManjuVijay wrote:

    Hi,

     

    I have done the same way. you can see in the code i have added. Rows is the package level variable I have used. In script I used Variables.Rows to access the value. when i write into a file it rights as 0

     

    Thanks

    the code should be:

    s = Dts.Variables("Rows").Value

    Monday, June 26, 2006 3:49 AM
  • Hi,

     

    I am getting error saying DTS is not declared.

     

    Thanks

    Monday, June 26, 2006 5:28 AM
  • Hi,

     

    I want to know whether i'm missing anyother thing.

    I beleive I have to set only the variable name in RowCount. Any thing else I have to do?

     

    Monday, June 26, 2006 5:31 AM
  • If i use script task it works properly

     

    why i am not able to do so in script transform component

    Monday, June 26, 2006 8:32 AM
  • The Script Task and Script Component are very different beasts. It is wrong to assume that because you can do something in one then you can also do the same in the other.

    Its also true to say there are different ways of doing the same thing. For example, the syntax for accessing variables in the script component is different to that for accessig them in the script task.

    What exactly are you unable to do?

    -Jamie

     

    Monday, June 26, 2006 8:46 AM
  • > If i use script task it works properly

     >why i am not able to do so in script transform component

    The script component is used within a DataFlow task. The DataFlow task "snapshots" a variable value when it begins execution and cannot modify the variable until it has completed.

    So, your row count = 0 at the beginning of execution. Your script component accesses the "snapshot" value and writes out 0.

    The RowCount component only updates the row count variable, when execution of the data flow has completed. Your script task accesses the value after this and writes out the final rowcount.

    Why does SSIS snapshot variable values? Well imagine a conditional split where the data is split on a variable value. If that could change during execution of a data flow, the behaviour of the split would be unpredictable - rows would be directed depending on whether they just happened to reach the split before or after the variable changed.

    Donald 

     

     

    Monday, June 26, 2006 4:19 PM