' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task.Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' ' Add your code here ' Dim sqlConn As SqlClient.SqlConnection sqlConn = CType(Dts.Connections("LocalHost.HNA_ODS1").AcquireConnection(Nothing), SqlClient.SqlConnection) Dim command As New SqlClient.SqlCommand command.Connection = sqlConn command.CommandType = CommandType.Text command.CommandText = "SELECT MAX_INC_SIGN FROM inc_sign WHERE TABLE_NAME = 'ODS_FOC_LEGS'" Dim SR As SqlClient.SqlDataReader = command.ExecuteReader() If SR.Read() Then Dts.Variables("ODS_FOC_LEGS_SQL").Value = "select a.*,sysdate as input_date,sysdate as modify_date from fleetwatch.legs a where to_char(to_date(std,'yyyy-MM-dd HH24:mi:ss') - 8/24,'yyyy-mm-dd') < to_char(sysdate,'yyyy-mm-dd') and to_char(to_date(std,'yyyy-MM-dd HH24:mi:ss') - 8/24,'yyyy-mm-dd') >= '" + SR.GetString(0) + "'" 'Windows.Forms.MessageBox.Show(CStr(Dts.Variables("ODS_FOC_LEGS_SQL").Value)) End If command = Nothing Dts.TaskResult = Dts.Results.Success End SubEnd Class
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain ' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J. Public Sub Main()
'
' Add your code here
'
Dim sqlConn As SqlClient.SqlConnection
sqlConn = CType(Dts.Connections("LocalHost.HNA_ODS1").AcquireConnection(Nothing), SqlClient.SqlConnection) Dim command As New SqlClient.SqlCommand
command.Connection = sqlConn
command.CommandType = CommandType.Text
command.CommandText = "SELECT MAX_INC_SIGN FROM inc_sign WHERE TABLE_NAME = 'ODS_FOC_LEGS'"
Dim SR As SqlClient.SqlDataReader = command.ExecuteReader()
If SR.Read() Then
Dts.Variables("ODS_FOC_LEGS_SQL").Value = "select a.*,sysdate as input_date,sysdate as modify_date from fleetwatch.legs a where to_char(to_date(std,'yyyy-MM-dd HH24:mi:ss') - 8/24,'yyyy-mm-dd') < to_char(sysdate,'yyyy-mm-dd') and to_char(to_date(std,'yyyy-MM-dd HH24:mi:ss') - 8/24,'yyyy-mm-dd') >= '" + SR.GetString(0) + "'" 'Windows.Forms.MessageBox.Show(CStr(Dts.Variables("ODS_FOC_LEGS_SQL").Value))
End If command = Nothing
Dts.TaskResult = Dts.Results.Success
End SubEnd Class