_ Public Class UserComponent Inherits ScriptComponentPlus Public Connections As New Connections(Me) Public Variables As New Variables(Me) Public OutputBuffer As OutputBuffer Public Overridable Sub FinishOutputs() End Sub Private Sub MarkOutputsFinished() If OutputBuffer IsNot Nothing Then OutputBuffer.SetEndOfRowset OutputBuffer = Nothing End If End Sub Public Overrides Sub PrimeOutput(ByVal Outputs As Integer, ByVal OutputIDs() As Integer, ByVal Buffers() As PipelineBuffer, ByVal OutputMap As OutputNameMap) For I As Integer = 0 To Outputs - 1 If OutputIDs(I) = GetOutputID(OutputMap, "Output") Then OutputBuffer = New OutputBuffer(Me, OutputIDs(I), False, Buffers(I), OutputMap) End If Next CreateNewOutputRows() FinishOutputs() MarkOutputsFinished() End Sub Public Overridable Sub CreateNewOutputRows() End Sub End Class Public Class Connections Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub End Class Public Class Variables Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub End Class ]]> _ Public Class OutputBuffer Inherits ScriptBufferPlus Public Sub New(ByVal Component As ScriptComponent, ByVal ObjectID As Integer, ByVal IsInput As Boolean, ByVal Buffer As PipelineBuffer, ByVal OutputMap As OutputNameMap) MyBase.New(Component, ObjectID, IsInput, Buffer, OutputMap) End Sub Public Overrides ReadOnly Property StaticInputColumns() As String() Get Return New String() {} End Get End Property Public WriteOnly Property [Timestamp]() As DateTime Set Me(0) = Value End Set End Property Public WriteOnly Property [Timestamp_IsNull] As Boolean Set If (value) SetNull(0) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Timezone]() As String Set Me(1) = Value End Set End Property Public WriteOnly Property [Timezone_IsNull] As Boolean Set If (value) SetNull(1) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Type]() As String Set Me(2) = Value End Set End Property Public WriteOnly Property [Type_IsNull] As Boolean Set If (value) SetNull(2) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Email]() As String Set Me(3) = Value End Set End Property Public WriteOnly Property [Email_IsNull] As Boolean Set If (value) SetNull(3) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Name]() As String Set Me(4) = Value End Set End Property Public WriteOnly Property [Name_IsNull] As Boolean Set If (value) SetNull(4) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [TransactionId]() As String Set Me(5) = Value End Set End Property Public WriteOnly Property [TransactionId_IsNull] As Boolean Set If (value) SetNull(5) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Status]() As String Set Me(6) = Value End Set End Property Public WriteOnly Property [Status_IsNull] As Boolean Set If (value) SetNull(6) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Amt]() As Decimal Set Me(7) = Value End Set End Property Public WriteOnly Property [Amt_IsNull] As Boolean Set If (value) SetNull(7) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [CurrencyCode]() As String Set Me(8) = Value End Set End Property Public WriteOnly Property [CurrencyCode_IsNull] As Boolean Set If (value) SetNull(8) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [FeeAmt]() As Decimal Set Me(9) = Value End Set End Property Public WriteOnly Property [FeeAmt_IsNull] As Boolean Set If (value) SetNull(9) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [NetAmt]() As Decimal Set Me(10) = Value End Set End Property Public WriteOnly Property [NetAmt_IsNull] As Boolean Set If (value) SetNull(10) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [DetailId]() As String Set Me(11) = Value End Set End Property Public WriteOnly Property [DetailId_IsNull] As Boolean Set If (value) SetNull(11) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [DetailName]() As String Set Me(12) = Value End Set End Property Public WriteOnly Property [DetailName_IsNull] As Boolean Set If (value) SetNull(12) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [DetailQty]() As String Set Me(13) = Value End Set End Property Public WriteOnly Property [DetailQty_IsNull] As Boolean Set If (value) SetNull(13) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public Overrides ReadOnly Property StaticOutputColumns() As String() Get Return New String() {"Timestamp", "Timezone", "Type", "Email", "Name", "TransactionId", "Status", "Amt", "CurrencyCode", "FeeAmt", "NetAmt", "DetailId", "DetailName", "DetailQty"} End Get End Property Public Overloads Sub AddRow() MyBase.AddRow() End Sub Public Overloads Sub SetEndOfRowset() MyBase.SetEndOfRowset() End Sub Public Overloads Function EndOfRowset() As Boolean EndOfRowset = MyBase.EndOfRowset End Function End Class ]]> _ _ _ _ _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Const LiveUrl As String = "https://api-3t.paypal.com/nvp" Private Const SandboxUrl As String = "https://api-3t.sandbox.paypal.com/nvp" Private Const ApiVersion As String = "85.0" Private Const MethodTrxSearch As String = "TransactionSearch" Private Const MethodGetDetails As String = "GetTransactionDetails" Private Const SubscriptionId As String = "SUBSCRIPTIONID" Private Const ReceiptId As String = "RECEIPTID" Private Const TimeStamp_1P As String = "L_TIMESTAMP{0}" Private Const TimeZone_1P As String = "L_TIMEZONE{0}" Private Const Type_1P As String = "L_TYPE{0}" Private Const Email_1P As String = "L_EMAIL{0}" Private Const Name_1P As String = "L_NAME{0}" Private Const TransactionId_1P As String = "L_TRANSACTIONID{0}" Private Const Status_1P As String = "L_STATUS{0}" Private Const Amt_1P As String = "L_AMT{0}" Private Const CurrencyCode_1P As String = "L_CURRENCYCODE{0}" Private Const FeeAmt_1P As String = "L_FEEAMT{0}" Private Const NetAmt_1P As String = "L_NETAMT{0}" Private Const ErrorCode_1P As String = "L_ERRORCODE{0}" Private Const LongMessage_1P As String = "L_LONGMESSAGE{0}" Private Const Qty_1P As String = "L_QTY{0}" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Enum ServerTypes Production Sandbox End Enum ' ServerTypes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Enum TransactionClassTypes All Sent Received MassPay MoneyRequest FundsAdded FundsWithdrawn Referral Fee Subscription Dividend Billpay Refund CurrencyConversions BalanceTransfer Reversal Shipping BalanceAffecting ECheck End Enum ' TransactionClassTypes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Enum StatusTypes Pending Processing Success Denied Reversed End Enum ' StatusTypes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Try ' Setup method parameters. Dim trxParams As String = _ "&STARTDATE=" + GetDateString_(m_startDate) + _ "&ENDDATE=" + GetDateString_(m_endDate) + _ "&TRANSACTIONCLASS=" + Me.TransactionClass.ToString() + _ "&STATUS=" + Me.Status.ToString() + _ "&" + Me.Other.Replace("\r\n", "&") Dim trxList As Hashtable = QueryPayPal_(MethodTrxSearch, trxParams) Dim index As Integer = 0 While trxList.Contains(String.Format(TimeStamp_1P, index)) With Me.OutputBuffer Call .AddRow() Dim trxId As String = CStr(trxList(String.Format(TransactionId_1P, index))) .Timestamp = Convert.ToDateTime(trxList(String.Format(TimeStamp_1P, index))) .Timezone = CStr(trxList(String.Format(TimeZone_1P, index))) .Type = CStr(trxList(String.Format(Type_1P, index))) .Email = CStr(trxList(String.Format(Email_1P, index))) .Name = CStr(trxList(String.Format(Name_1P, index))) .TransactionId = trxId .Status = CStr(trxList(String.Format(Status_1P, index))) .Amt = Convert.ToDecimal(trxList(String.Format(Amt_1P, index))) .CurrencyCode = CStr(trxList(String.Format(CurrencyCode_1P, index))) .FeeAmt = Convert.ToDecimal(trxList(String.Format(FeeAmt_1P, index))) .NetAmt = Convert.ToDecimal(trxList(String.Format(NetAmt_1P, index))) If Me.GetDetails Then ' Retrieve transaction details. Dim detailParams As String = "&TRANSACTIONID=" + trxId Dim trxDetails As Hashtable = QueryPayPal_(MethodGetDetails, detailParams) Dim subscrId As String = CStr(trxDetails(SubscriptionId)) Dim recId As String = CStr(trxDetails(ReceiptId)) ' Setup detail id. If Not String.IsNullOrEmpty(subscrId) Then .DetailId = subscrId ElseIf Not String.IsNullOrEmpty(recId) Then .DetailId = recId Else .DetailId = trxId End If ' Setup other details. .DetailName = GetNVPList_(trxDetails, Name_1P) .DetailQty = GetNVPList_(trxDetails, Qty_1P) End If End With index += 1 End While Catch ex As Exception Call FireError_(ex.Message) End Try End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.User) Then Throw New ApplicationException("Specify user name.") End If If String.IsNullOrEmpty(Me.Password) Then Throw New ApplicationException("Specify password.") End If If String.IsNullOrEmpty(Me.Signature) Then Throw New ApplicationException("Specify signature.") End If If String.IsNullOrEmpty(m_startDate) Then Throw New ApplicationException("Specify start date.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList ' Setup static. Call result.Add("ServerType") Call result.Add("User") Call result.Add("Password") Call result.Add("Signature") Call result.Add("Timeout") Call result.Add("GetDetails") Call result.Add("TransactionClass") Call result.Add("Status") Call result.Add("Other") ' Setup dynamic. Call result.Add("IsStartDateVariable") Call result.Add(IIf(Me.IsStartDateVariable, "StartDateVariable", "StartDate")) Call result.Add("IsEndDateVariable") Call result.Add(IIf(Me.IsEndDateVariable, "EndDateVariable", "EndDate")) Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property ServerType() As ServerTypes Get ServerType = m_serverType End Get Set(ByVal value As ServerTypes) m_serverType = value End Set End Property ' ServerType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property User() As String Get User = m_user End Get Set(ByVal value As String) m_user = value End Set End Property ' User ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Password() As String Get Password = m_pwd End Get Set(ByVal value As String) m_pwd = value End Set End Property ' Password ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property Signature() As String Get Signature = m_signature End Get Set(ByVal value As String) m_signature = value End Set End Property ' Signature ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Timeout() As Integer Get Timeout = m_timeout End Get Set(ByVal value As Integer) m_timeout = value End Set End Property ' Timeout ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property GetDetails() As Boolean Get GetDetails = m_getDetails End Get Set(ByVal value As Boolean) m_getDetails = value End Set End Property ' GetDetails ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property IsStartDateVariable() As Boolean Get IsStartDateVariable = m_isStartDateVar End Get Set(ByVal value As Boolean) If m_isStartDateVar <> value Then m_isStartDateVar = value m_startDate = String.Empty End If End Set End Property ' IsStartDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property StartDate() As DateTime Get Dim result As DateTime If Not String.IsNullOrEmpty(m_startDate) Then result = Convert.ToDateTime(m_startDate) End If StartDate = result End Get Set(ByVal value As DateTime) m_startDate = value.ToString() End Set End Property ' StartDate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property StartDateVariable() As String Get StartDateVariable = m_startDate End Get Set(ByVal value As String) m_startDate = value End Set End Property ' StartDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property IsEndDateVariable() As Boolean Get IsEndDateVariable = m_isEndDateVar End Get Set(ByVal value As Boolean) If m_isEndDateVar <> value Then m_isEndDateVar = value m_endDate = String.Empty End If End Set End Property ' IsEndDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property EndDate() As DateTime Get Dim result As DateTime If Not String.IsNullOrEmpty(m_endDate) Then result = Convert.ToDateTime(m_endDate) End If EndDate = result End Get Set(ByVal value As DateTime) m_endDate = value.ToString() End Set End Property ' EndDate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property EndDateVariable() As String Get EndDateVariable = m_endDate End Get Set(ByVal value As String) m_endDate = value End Set End Property ' EndDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property TransactionClass() As TransactionClassTypes Get TransactionClass = m_trxClass End Get Set(ByVal value As TransactionClassTypes) m_trxClass = value End Set End Property ' TransactionClass ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Status() As StatusTypes Get Status = m_status End Get Set(ByVal value As StatusTypes) m_status = value End Set End Property ' Status ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Other() As String Get Other = m_other End Get Set(ByVal value As String) m_other = value End Set End Property ' Other #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean Call Me.ComponentMetaData.FireError(0, "PayPal Trx Source", message, String.Empty, 0, cancel) End Sub ' FireError_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function QueryPayPal_(ByVal method As String, ByVal params As String) As Hashtable Dim result As Hashtable ' Setup server URL. Dim serverUrl As String = String.Empty Select Case Me.ServerType Case ServerTypes.Production serverUrl = LiveUrl Case ServerTypes.Sandbox serverUrl = SandboxUrl Case Else Throw New Exception("Unhandled server type.") End Select params = _ "USER=" + Me.User + _ "&PWD=" + Me.Password + _ "&SIGNATURE=" + Me.Signature + _ params params = _ "METHOD=" + method + "&" + _ "VERSION=" + ApiVersion + "&" + _ GetEncodedParams_(params) ' Setup web request. Dim req As HttpWebRequest = CType(WebRequest.Create(serverUrl), HttpWebRequest) req.Method = "POST" req.ContentType = "application/x-www-form-urlencoded" req.Timeout = Me.Timeout Using reqStream As Stream = req.GetRequestStream() Dim reqBytes() As Byte = Encoding.UTF8.GetBytes(params) Call reqStream.Write(reqBytes, 0, reqBytes.Length) End Using ' Get result. Dim resResult As String = String.Empty Dim res As WebResponse = req.GetResponse() Using resStream As Stream = res.GetResponseStream() resResult = New StreamReader(resStream).ReadToEnd() End Using ' Process result. result = GetDictionaryFromNVP_(resResult) If CStr(result("ACK")) <> "Success" Then ' Method call failed. Report error. Throw New Exception(GetErrorFromNVP_(result)) End If QueryPayPal_ = result End Function ' QueryPayPal_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetDictionaryFromNVP_(ByVal listStr As String) As Hashtable Dim result As Hashtable = New Hashtable() Dim list() As String = HttpUtility.UrlDecode(listStr).Split( _ New String() {"&"}, _ StringSplitOptions.RemoveEmptyEntries) For Each item As String In list Dim sepIndex As Integer = item.IndexOf("=") Dim name As String = item.Substring(0, sepIndex) Dim value As String = item.Substring(sepIndex + 1) result(name) = value Next GetDictionaryFromNVP_ = result End Function ' GetDictionaryFromNVP_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Escape special characters in parameters. Private Function GetEncodedParams_(ByVal params As String) As String Dim result As String = String.Empty Dim paramsList As Hashtable = GetDictionaryFromNVP_(params) For Each param As DictionaryEntry In paramsList If Not String.IsNullOrEmpty(result) Then result += "&" End If result += String.Format( _ "{0}={1}", _ param.Key, _ HttpUtility.UrlEncode(CStr(param.Value))) Next GetEncodedParams_ = result End Function ' GetEncodedParams_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetDateString_(ByVal dateStr As String) As String Dim result As String = String.Empty If Not String.IsNullOrEmpty(dateStr) Then result = Convert.ToDateTime(dateStr).ToString("yyyy-MM-dd") + "T00:00:00Z" End If GetDateString_ = result End Function ' GetDateString_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetErrorFromNVP_(ByVal resList As Hashtable) As String Dim result As String = String.Empty Dim index As Integer = 0 While resList.Contains(String.Format(ErrorCode_1P, index)) If index > 0 Then result += "\r\n" End If result += String.Format( _ "Error code: {0}, Message: {1}", _ resList(String.Format(ErrorCode_1P, index)), _ resList(String.Format(LongMessage_1P, index))) index += 1 End While GetErrorFromNVP_ = result End Function ' GetErrorFromNVP_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of names combined into a string. Private Function GetNVPList_(ByVal resList As Hashtable, ByVal name As String) As String Dim result As String = String.Empty Dim index As Integer = 0 While resList.Contains(String.Format(name, index)) If index > 0 Then result += "|" End If result += CStr(resList(String.Format(name, index))) index += 1 End While GetNVPList_ = result End Function ' GetNVPList_ #End Region ' Internals #Region "Attributes" Private m_serverType As ServerTypes Private m_user As String Private m_pwd As String Private m_signature As String Private m_timeout As Integer Private m_getDetails As Boolean Private m_isStartDateVar As Boolean Private m_startDate As String Private m_isEndDateVar As Boolean Private m_endDate As String Private m_trxClass As TransactionClassTypes Private m_status As StatusTypes Private m_other As String #End Region ' Attributes End Class ' ScriptMain ]]> msBuild ScriptComponent_27d6c3880b784c8e8acf997642a480a6 ScriptComponent_27d6c3880b784c8e8acf997642a480a6 {895A116E-D8AD-4618-BB26-4F4EA9EAF4F6} ]]> {30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F} Debug AnyCPU 8.0.30703 2.0 {D617D226-D276-482A-B315-C0A9DF9083DF} Library My Project ScriptComponent_27d6c3880b784c8e8acf997642a480a6 ScriptComponent_27d6c3880b784c8e8acf997642a480a6 v4.0 512 Binary On On true full false .\bin\Debug\ false true true prompt 4 false true .\bin\Release\ false false true prompt 4 False C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\CozyRoc.SSISPlus.2014.dll False False False False SSIS_SC120 ]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_27d6c3880b784c8e8acf997642a480a6VisualBasic