Using Script Component Instead of Cursor

There are many scenarios where we use cursor in our DW building logic.
But as all we know that cursor is nothing but a curse for a developer as its impact on performance is huge.
We always try to avoid it. But cant refused to use it.

Here I am taking a scenario which I faced in a project.

Supposed you have to dates like Order date and Ship Date and you have to count the no of days between order and shipment.
Yes you can simply use DateDiff Function,
But the problem with this is like If someone want to see the no of days on the date which is in between Order and Shipment date. Then??

For cases like these where you need to insert dates in between two dates I used cursor.
But the performance of cursor was too bad.

Then I thought of alternative approach …which brought me to the Script Component.
Yes I tried Script Task instead of Cursor and I found it very fast and well resource utilized.


Following is the script you can use:-

Here I am taking Script Component as Source.
Output records can be stored in any table by using any of the SSIS destination components.

Imports System

Imports System
Imports System.Data
Imports System.DateTime
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.String
Imports System.Text


Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim dateinsrt As DateTime
Dim loopend As DateTime
Dim Sqlconn As SqlConnection
Dim Sqlcmd As SqlCommand


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim connectionString As String
connMgr = Me.Connections.STGConnection
Sqlconn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub

Public Overrides Sub CreateNewOutputRows()
Sqlcmd = New SqlCommand("SELECT SalesOrderID,OrderDate,ShipDate FROM Sales.SalesOrderHeader", Sqlconn)
Dim reader As SqlDataReader
reader = Sqlcmd.ExecuteReader()
Do While reader.Read
With MyOutputBuffer
dateinsrt = CDate(reader.Item(1))
If IsDBNull(reader.Item(2)) Then 'In Case if shipment date is NUll ie not yet shipped
loopend = Today()
Else
loopend = CDate(reader.Item(2))
End If
While dateinsrt <= loopend  'loop period 
.AddRow()
'Output Records
.SalesOrderID = CInt(reader.Item(0))
.OrderDate = CDate(reader.Item(1))
.ShipDate = CDate(reader.Item(2))
.NoOfDays = 1
.DateKey = dateinsrt
dateinsrt = dateinsrt.AddDays(1) 'Adding days
End While
End With
Loop
reader.Close()
End Sub
End Class

Your Feedbacks are valuable ............ :-)


Thanks,
Gaurav Gupta