[SSIS] : STDEV in Derived Columns

Hello,

Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?

Any help is welcome ;-)

Cheers,

Bertrand

Bertrandr wrote:

Hello,

Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?

Any help is welcome ;-)

Cheers,

Bertrand

Have you looked into the documentation?

http://msdn2.microsoft.com/en-us/library/ms141671.aspx

it looks to me thta function is not available...you may want to do that in the DB or create a script component.

|||

Haven't calculated standard deviation in a derived column, but rather in an Async script component (i.e. Synchronous Input Id = 0 on the first output). The data being passed in to this transform is DT_CY data type (currency) called TotalDue. The data passed out is a DT_R8 on the output called StdDev.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private rowCount As Integer = 0

Private columnSum As Double = 0

Private columnSumOfSquares As Double = 0

Public Overrides Sub InputMain_ProcessInput(ByVal Buffer As InputMainBuffer)

While Buffer.NextRow()

InputMain_ProcessInputRow(Buffer)

End While

If Buffer.EndOfRowset Then

With OutputMainBuffer

.AddRow()

.StdDev = StdDev(rowCount, columnSum, columnSumOfSquares)

End With

OutputMainBuffer.SetEndOfRowset()

End If

End Sub

Public Overrides Sub InputMain_ProcessInputRow(ByVal Row As InputMainBuffer)

rowCount = rowCount + 1

columnSum = columnSum + Row.TotalDue

columnSumOfSquares = columnSumOfSquares + Math.Pow(Row.TotalDue, 2)

End Sub

Private Function StdDev(ByVal Rows As Integer, ByVal sum As Double, ByVal sumOfSquares As Double) As Double

Dim topSumVal As Double = (rowCount * columnSumOfSquares) - (Math.Pow(columnSum, 2))

Return Math.Sqrt(topSumVal / (rowCount * (rowCount - 1)))

End Function

End Class

|||Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253840

Thanks,
Phil|||

Phil Brammer wrote:

Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=253840

Thanks,
Phil

Phil,

Are you sure that's the right link? it takes me to 'Page Not Found'.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=253840

Thanks,
Phil

Phil,

Are you sure that's the right link? it takes me to 'Page Not Found'.

-Jamie

Good catch... When I visited the link, it redirected to a URL without "SQLServer" in the path... The link has been updated in my original post...