Share via

Asynchronous Output in SSIS

  • Article

Hi Folks,
I was supposed to do a Currency Currency Required for my Cubes.This Could have been achieved using Currency Conversion Methods in Cube itself.
Thought of giving a try in ETL(Integration Services) itself as it will be easy and Fast.

I am using a Asynchronous Buffer Output to achieve the Same.Unable to post the original Data on Web have made a sample Query to Show the Method of achieving it.

Settings which Needs to be done if its Asynchronous output.
Note :Sync InputID is set to None.

Script used to Achieve Conversion using AddRow Method.

 ' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

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


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'If the incoming Row is Rupee then convert it into Dollar and Yen ,Keep Rest of the Fields Same.
'Incase if you have ProductID,RegionID or any fields Equate input Buffer to Output Buffer .
If (Row.Cur = "Rupee") Then
'Add a New Row for Every Input Buffer
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue * 0.02
Output0Buffer.Cur = "Dollar"
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue * 0.625
Output0Buffer.Cur = "Yen"
'Add the Row to Output Buffer which is same as Input buffer
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue
Output0Buffer.Cur = Row.Cur
End If

'Same Applies for Yen
If (Row.Cur = "Yen") Then
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue * 0.0125
Output0Buffer.Cur = "Dollar"
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue * 1.6
Output0Buffer.Cur = "Rupee"
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue
Output0Buffer.Cur = Row.Cur
End If


'Same Applies for Dollar
If (Row.Cur = "Dollar") Then
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue * 50
Output0Buffer.Cur = "Rupee"
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue * 80
Output0Buffer.Cur = "Yen"
Output0Buffer.AddRow()
Output0Buffer.Value = Row.ConvValue
Output0Buffer.Cur = Row.Cur
End If


End Sub

End Class

End Result

As you can see Every Single Row(4 Rows Input) has Yielded 3 Rows (3*4=12 Rows output)