FTP Download and DB Update Using SSIS
In this Article, we will see how to Dowload the FTP file to Local Folder and Update all files into DB. The below picture illustrate the overall view,


We need to declare the following variables. Its scope is Global.



 And also we need to add the connection Managers for Moving files, Updating files into DB and copy Folder and FTP Download. Please Visit the below figures for configuration

 For FTP
 
For DB Connection


For Folder Creation


 For DB Insert



 For Mail Send
 After Completing our Configurations, We are coming to the Task. Our First Task is Download the files from FTP, For FTP Download I have used the script task to get the files and Folder List. The below figure show the configurations




The Script Task Code is given below

FTP Connections


Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Collections

Public Class ScriptMain

Public Sub Main()

Dim ftp As FtpClientConnection
Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections("FTP")
'Set the properties like username & password
'cm.Properties("ServerName").SetValue(cm, "Test")
'cm.Properties("ServerUserName").SetValue(cm, "abcd")
'cm.Properties("ServerPassword").SetValue(cm, "xxxx")
'cm.Properties("ServerPort").SetValue(cm, "80")
'cm.Properties("Timeout").SetValue(cm, "100") 'The 0 setting will make it not timeout
'cm.Properties("ChunkSize").SetValue(cm, "1") '1000 kb
'cm.Properties("Retries").SetValue(cm, "5")
'create the FTP object that sends the files and pass it the connection created above.

ftp = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server

ftp.Connect()
'Sets the Working Directory as your share name
ftp.SetWorkingDirectory(Dts.Variables("FTPWorkingDirectory").Value.ToString())
'ftp the file
Dim sFolderNames() As String
Dim sFileNames() As String

'Get the Folders and Files List in that Share path
ftp.GetListing(sFolderNames, sFileNames)
If (sFolderNames Is Nothing) Then
Dts.TaskResult = Dts.Results.Failure
Else
' Copy StringArray to ArrayList to fit in Object variable
Dim folderNamesArray As ArrayList = New ArrayList(sFolderNames)
' Optional sorter
folderNamesArray.Sort()
' Fill ssis object variable
Dts.Variables("FTPDirectoryList").Value = folderNamesArray
'Dim iCount As Integer
'For iCount = 0 To sFolderNames.Length - 1
'Directory.CreateDirectory(Dts.Variables("DownloadDirectory").Value & sFolderNames(iCount))
'Next iCount
''Build a array of all the file names that is going to be FTP'ed (in this case only one file)

'Dim files(0) As String
'files(0) = "rpt/DcnRpt/Test1/*.*"
'Dim objDirectory As New DirectoryInfo(sDestinationPath)
'Directory.GetCurrentDirectory()

''Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.
'ftp.ReceiveFiles(files, sDestinationPath, True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII

Dts.TaskResult = Dts.Results.Success
End If

Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
'Close Connection
ftp.Close()

End Sub

End Class


Here I have used the Foreach container Loop for getting folder with in the folder list and its files then copy that into Local path.The figure illustrates the configuration






 For download the script code  is given below,

Create FTPDirectory into LocalDirectory


Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient

Public Class ScriptMain
Public Sub Main()


Try

Dim sLocatDirectory As String
Dim sRemotePath As String

sLocatDirectory = Dts.Variables("DownloadDirectory").Value.ToString() & "\" & Dts.Variables("FTPDirectoryList").Value.ToString()

sLocatDirectory = "\" & sLocatDirectory.Replace("\\", "\")
Directory.CreateDirectory(sLocatDirectory)
Dts.Variables("LocalDownloadPath").Value = sLocatDirectory

sRemotePath = Dts.Variables("FTPWorkingDirectory").Value.ToString() & Dts.Variables("FTPDirectoryList").Value.ToString()

Dts.Variables("FTPCurrentWorkingDirectory").Value = sRemotePath

'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections("FTP")

'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "luna")
cm.Properties("ServerUserName").SetValue(cm, "abcd")
cm.Properties("ServerPassword").SetValue(cm, "xxxx")
cm.Properties("ServerPort").SetValue(cm, "80")
cm.Properties("Timeout").SetValue(cm, "300") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1") '1000 kb
cm.Properties("Retries").SetValue(cm, "5")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server
ftp.Connect()

'Sets the Working Directory as your share name
ftp.SetWorkingDirectory(sRemotePath)
'ftp the file
Dim sFolderNames() As String
Dim sFileNames() As String
'Get the Folders and Files List in that Share path
ftp.GetListing(sFolderNames, sFileNames)
ftp.ReceiveFiles(sFileNames, sLocatDirectory, True, False)
cm.Dispose()
ftp.Close()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

End Class


If We failure to getting a FTP File list at initial stage,we will send Failure mail to our Team.


 After downloading the files to Local , we will backup all of the folders and files.
 BackUp in Local Server

 Again we will get the Full process list for DB Updation using script Task (Get the Process Directory List)



                      

Get the Process Directory List Code,

 

Get the Current Process Directory List here


Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()
'Dim Files As FileInfo() = Root.GetFiles("*.*")
Dim sLocalSourceRootDirectory As String = Dts.Variables("DownloadDirectory").Value.ToString()

Dim Root As New DirectoryInfo(sLocalSourceRootDirectory)
Dim DirectoryList As DirectoryInfo()
DirectoryList = Root.GetDirectories("*.*")
' Copy StringArray to ArrayList to fit in Object variable
Dim folderNamesArray As ArrayList = New ArrayList(DirectoryList)
' Optional sorter
'folderNamesArray.Sort()
' Fill ssis object variable
Dts.Variables("FTPDirectoryList").Value = folderNamesArray
'Dim iCount As Integer
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

 Now Loop the Folder to Update the All files





Within that Loop We get the All inner Folders and process the list using inner Foreach container Loop.


. The script code is mentioned below,


Getting Local File List


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Collections

Public Class ScriptMain

Public Sub Main()
'Dim Files As FileInfo() = Root.GetFiles("*.*")
Dim sFileList As String = Dts.Variables("DownloadDirectory").Value.ToString() & "\" & Dts.Variables("FTPDirectoryList").Value.ToString()

Dim Root As New DirectoryInfo(sFileList)
' Copy StringArray to ArrayList to fit in Object variable
Dim folderNamesArray As ArrayList = New ArrayList(Root.GetFiles())

' Optional sorter
'folderNamesArray.Sort()

' Fill ssis object variable
Dts.Variables("LocalFileList").Value = folderNamesArray
'Dim iCount As Integer
Dts.TaskResult = Dts.Results.Success

End Sub

End Class





Update the connection Dynamically


Imports System
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()
Dim sCurrentFilePath As String = Dts.Variables("DownloadDirectory").Value.ToString() & "\" & Dts.Variables("FTPDirectoryList").Value.ToString() & "\" & Dts.Variables.Item("LocalFileList").Value.ToString()
Dts.Variables("UpdatePath").Value = sCurrentFilePath
Dts.Connections.Item("BulkInsert").ConnectionString = sCurrentFilePath
Dts.TaskResult = Dts.Results.Success
End Sub
End Class





Finally the files are updated into DB and Back of FTP files are in Local server.
 
07:40 | Category: | 0 comments

Comments (0)

Post a Comment