MSBI (SSIS/SSRS/SSAS) Online Training

Friday, January 30, 2009

DATE CONVERSION SCRIPT:à

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

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)

Dim month As String
Dim day As String
Dim j As Int32

month = Row.CalendarMonth.ToString
For j = 1 To (2 - Len(month))
month = "0" & month
Next

day = Row.CalendarDayMonth.ToString
For j = 1 To (2 - Len(day))
day = "0" & day
Next

Row.DateId = CInt(Row.CalendarYear.ToString + month + day)


End Sub

End Class

An other option is a script task. Instead of pulling the variables from the child package, you can push them from the parent package:-

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
' Call child package and pass through parameters / variables
' The child package is called with the same connection
' manager as the Execute Package Task would do
Try
' Configure the child package
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
Dim package As Package
package = app.LoadPackage(Dts.Connections("child.dtsx").ConnectionString.ToString(), Nothing)
package.Variables("User::variable1").Value = "some value"
' Execute package and return result
Dim result As DTSExecResult
result = package.Execute()
Dts.TaskResult = result
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class

No comments: