March 30, 2006 at 11:02 am
I am looking for examples using the Script Task, VB.NET and using ADO connections with record sets. Thank for any help.
April 3, 2006 at 8:00 am
This was removed by the editor as SPAM
April 5, 2006 at 7:48 am
Here is a code sample that reads a recordset and checks for the existence of mandatory files I am passing in Package Variables for the Database Server Name and location of the files I'm checking for.
Hope it helps....
' Microsoft SQL Server Integration Services Script Task
Imports System
Imports
System.Data
Imports
System.Math
Imports
System.IO
Imports
system.Data.SqlClient
Imports
Microsoft.SqlServer.Dts.Runtime
Public
Class ScriptMain
Public Sub Main()
Dim objCmd As New SqlCommand
Dim objConn As New SqlConnection
Dim objDR As SqlDataReader
Dim objFile As File
Dim strSQL As String
Dim strConnection As String
Dim strServerName As String
Dim strInterfacePath As String
Dim strFile As String
strServerName = Dts.Variables(
"User::strDBServerName").Value.ToString
strConnection =
"Data Source=" & strServerName & ";Database=DB01;Integrated Security=SSPI;"
strInterfacePath = Dts.Variables(
"User::strInterfacePath").Value.ToString
objConn.ConnectionString = strConnection
objConn.Open()
strSQL =
"SELECT Package_Name,File_Path,Data_File_Name,Count_File_Name " & _
"FROM Interface_Processing " & _
"WHERE Mandatory_Flag = 'Y' " & _
"AND Active_Ind = 'Y' " & _
"AND Interface_Frequency_Code <> 'W' " & _
"AND Data_File_Name IS NOT NULL " & _
"AND ((Last_Process_Date IS NULL) OR (Last_Process_Date < dbo.ufn_GetDateOnly(GetDate()))) " & _
"ORDER BY Sequence_ID "
With objCmd
.CommandText = strSQL
.CommandType = CommandType.Text
.Connection = objConn
objDR = .ExecuteReader
End With
With objDR
While .Read
' ==========================
' Check if Data File Exists
' ==========================
strFile = strInterfacePath &
"\" & CStr(.Item("File_Path")) & "\" & CStr(.Item("Data_File_Name"))
If Not objFile.Exists(strFile) Then
' Clean up
objDR.Close()
objCmd.Dispose()
objConn.Dispose()
Dts.TaskResult = Dts.Results.Success
Exit Sub
End If
' ==========================
' Check if Count File Exists
' ==========================
If Not IsDBNull(.Item("Count_File_Name")) Then
strFile = strInterfacePath &
"\" & CStr(.Item("File_Path")) & "\" & CStr(.Item("Count_File_Name"))
If Not objFile.Exists(strFile) Then
' Clean up
objDR.Close()
objCmd.Dispose()
objConn.Dispose()
Dts.TaskResult = Dts.Results.Success
Exit Sub
End If
End If
End While
' =============================================
' All mandatory files available for processing
' =============================================
Dts.Variables(
"User::strOLAPProcessingFlag").Value = "Y"
' Clean up
objDR.Close()
objCmd.Dispose()
End With
' Clean up
objConn.Dispose()
Dts.TaskResult = Dts.Results.Success
End Sub
April 10, 2006 at 12:23 pm
Thanks for the help.
Jim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply