file get from ftp using script

• manavairajan

  Right there with Babe

  Points: 781

  More actions

  April 6, 2011 at 10:09 am

  #234404

  Imports System

  Imports System.Data

  Imports System.Math

  Imports Microsoft.SqlServer.Dts.Runtime

  Public Class ScriptMain

  Public Const Constring As String = "Provider=SQLOLEDB.1;User ID=;Password=;Initial Catalog=;Data Source="

  Public Conn As System.Data.OleDb.OleDbConnection

  ' 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

  '

  AssignFileName("\SSM\")

  AssignFileName("\TTV\WindSor\")

  Dts.TaskResult = Dts.Results.Success

  End Sub

  Public Sub AssignFileName(ByVal strSource As String)

  Dim Fso As FileIO.FileSystem

  Fso = New FileIO.FileSystem

  Dim LogTable As System.Data.DataTable

  Dim Adaptr As System.Data.OleDb.OleDbDataAdapter

  LogTable = New System.Data.DataTable("LogTable")

  OpenConnection()

  Adaptr = New OleDb.OleDbDataAdapter("Select * from tbldataloadfiles", Conn)

  Adaptr.Fill(LogTable)

  Fso = New FileIO.FileSystem

  Dim iEnum As Collections.IEnumerator

  Dim filename As String

  Dim strFileDate As String

  Dim FileDate As Date

  iEnum = Fso.GetFiles(strSource).GetEnumerator()

  Dim drAddr() As DataRow

  While iEnum.MoveNext

  filename = iEnum.Current.ToString().Replace(strSource, "")

  drAddr = LogTable.Select("VchFileName='" & filename & "'")

  If drAddr.Length = 0 Then

  Dim Cmd As OleDb.OleDbCommand

  Cmd = New System.Data.OleDb.OleDbCommand("Insert into tbldataloadfiles (VchFileName) values ( '" & filename & "' )", Conn)

  Cmd.ExecuteNonQuery()

  Cmd = Nothing

  End If

  End While

  End Sub

  Public Sub OpenConnection()

  Try

  Conn = New System.Data.OleDb.OleDbConnection(Constring)

  If Conn.State = ConnectionState.Closed Then Conn.Open()

  Catch ex As Exception

  Throw New Exception(ex.ToString)

  End Try

  End Sub

  End Class

• Lowell

  SSC Guru

  Points: 323527

  More actions

  April 6, 2011 at 12:13 pm

  #1308192

  is there a question?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!