October 7, 2010 at 11:46 am
I'm trying to use in a transformation
But Also I'm trying to use the input data as a output.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Runtime
Public Class DestinationComponent
Inherits UserComponent
Public Overloads Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer, ByVal buffer As PipelineBuffer)
'
Dim q1 As Integer, q2 As Integer, q3 As Integer, q4 As Integer, q5 As Integer, q6 As Integer, q7 As Integer, q8 As Integer, q9 As Integer, q10 As Integer, q11 As Integer, qcount As Integer, ql2count As Integer, qovcount As Integer, qallcount As Integer
If Row.R1 = 0 Then
q1 = 0
Else
q1 = 1
End If
If Row.R2 = 0 Then
q2 = 0
Else
q2 = 1
End If
If Row.R3 = 0 Then
q3 = 0
Else
q3 = 1
End If
If Row.R4 = 0 Then
q4 = 0
Else
q4 = 1
End If
If Row.R5 = 0 Then
q5 = 0
Else
q5 = 1
End If
If Row.R6 = 0 Then
q6 = 0
Else
q6 = 1
End If
If Row.R7 = 0 Then
q7 = 0
Else
q7 = 1
End If
If Row.R8 = 0 Then
q8 = 0
Else
q8 = 1
End If
If Row.R9 = 0 Then
q9 = 0
Else
q9 = 1
End If
If Row.R10 = 0 Then
q10 = 0
Else
q10 = 1
End If
If Row.R11 = 0 Then
q11 = 0
Else
q11 = 1
End If
''q12 = 0
qcount = q1 + q2 + q3
ql2count = q4 + q5 + q6
qovcount = q8 + q9 + q10
qallcount = q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10
Output0Buffer.SurveyAnswerQ1Count = q1
Output0Buffer.SurveyAnswerQ2Count = q2
Output0Buffer.SurveyAnswerQ3Count = q3
Output0Buffer.SurveyAnswerQ4Count = q4
Output0Buffer.SurveyAnswerQ5Count = q5
Output0Buffer.SurveyAnswerQ6Count = q6
Output0Buffer.SurveyAnswerQ7Count = q7
Output0Buffer.SurveyAnswerQ8Count = q8
Output0Buffer.SurveyAnswerQ9Count = q9
Output0Buffer.SurveyAnswerQ10Count = q10
Output0Buffer.SurveyAnswerQ11Count = q11
'Output0Buffer.SurveyAnswerQ12Count = q12
Output0Buffer.SurveyAnswerAllcount = qallcount
If Row.R1 = 0 Then
Output0Buffer.SurveyAnswerQ1AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ1AvgRating = CInt(Row.R1)
End If
If Row.R2 = 0 Then
Output0Buffer.SurveyAnswerQ2AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ2AvgRating = CInt(Row.R2)
End If
If Row.R3 = 0 Then
Output0Buffer.SurveyAnswerQ3AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ3AvgRating = CInt(Row.R3)
End If
If Row.R4 = 0 Then
Output0Buffer.SurveyAnswerQ4AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ4AvgRating = CInt(Row.R4)
End If
If Row.R5 = 0 Then
Output0Buffer.SurveyAnswerQ5AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ5AvgRating = CInt(Row.R5)
End If
If Row.R6 = 0 Then
Output0Buffer.SurveyAnswerQ6AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ6AvgRating = CInt(Row.R6)
End If
If Row.R7 = 0 Then
Output0Buffer.SurveyAnswerQ7AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ7AvgRating = CInt(Row.R7)
End If
If Row.R8 = 0 Then
Output0Buffer.SurveyAnswerQ8AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ8AvgRating = CInt(Row.R8)
End If
If Row.R9 = 0 Then
Output0Buffer.SurveyAnswerQ9AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ9AvgRating = CInt(Row.R9)
End If
If Row.R10 = 0 Then
Output0Buffer.SurveyAnswerQ10AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ10AvgRating = CInt(Row.R10)
End If
If Row.R11 = 0 Then
Output0Buffer.SurveyAnswerQ11AvgRating = CInt("")
Else
Output0Buffer.SurveyAnswerQ11AvgRating = CInt(Row.R11)
End If
'-------------------------------------------------------------------------------------------------------
If buffer.IsNull(CInt(Row.ServiceDeskRating)) Then
If qcount = 0 Then
Output0Buffer.SurveyAnswerSDAvgRating = CSng(Row.ServiceDeskRating)
Else
Output0Buffer.SurveyAnswerSDAvgRating = CSng((Row.R1 + Row.R2 + Row.R3) / qcount)
End If
Else
Output0Buffer.SurveyAnswerSDAvgRating = CSng(Row.ServiceDeskRating)
End If
'-------------------------------------------------------------------------------------------------------
If ql2count = 0 Then
Output0Buffer.SurveyAnswerL2AvgRating = CSng("")
Else
Output0Buffer.SurveyAnswerL2AvgRating = CSng((Row.R4 + Row.R5 + Row.R6) / ql2count)
End If
'-------------------------------------------------------------------------------------------------------
If qovcount = 0 Then
Output0Buffer.SurveyAnswerOvAvgRating = CSng("")
Else
Output0Buffer.SurveyAnswerOvAvgRating = CSng((Row.R8 + Row.R9 + Row.R10) / qovcount)
End If
'-------------------------------------------------------------------------------------------------------
If qallcount = 0 Then
Output0Buffer.SurveyAnswerAllAvgRating = CSng("")
Else
Output0Buffer.SurveyAnswerAllAvgRating = CSng((Row.R1 + Row.R2 + Row.R3 + Row.R4 + Row.R5 + Row.R6 + Row.R7 + Row.R8 + Row.R9 + Row.R10 + Row.R11))
End If
Output0Buffer.ReceivedYear = Row.CompletedSurveyDate.Year
Output0Buffer.ReceivedMonth = Row.CompletedSurveyDate.Month
Output0Buffer.SurveyAnswerCount = 1
End Sub
End Class
October 7, 2010 at 1:52 pm
... and the question is? 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 7, 2010 at 2:24 pm
Sorry,
when I add the synchronousInput = input "OE" (3867). the csatBuffer.q1count = q1 appear an error name csatbuffer is not declared.
if I remove the synchronousInput everything works fine but I can't see the input fields(OE).
Please excuse me
October 7, 2010 at 2:27 pm
Sorry,
when I add the synchronousInput = input "OE" (3867). the csatBuffer.q1count = q1 appear an error name csatbuffer is not declared.
if I remove the synchronousInput everything works fine but I can't see the input fields(OE).
Please excuse me
October 18, 2010 at 5:35 am
I assume this is in SSIS? You're using the Script Transformation Editor?
What exactly does your input and output data look like (examples, please)? Are you pulling from SQL Server or putting into SQL Server or doing both?
Source and destination types would help us understand your issue.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply