Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts

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

Send Email Using SSIS
In this Article, I have explained to send a mail using script task.  Go to File à New and Open a SSIS Package like below,


Drag and drop the scrip task and rename it


In script Task, right click and edit it
 Then we will get the below screen. Here Give a Script task name and description.


And then the Scrip Table Click the Design Script 


It will open a new window like below


Here we will do the code like our .Net Code. See the below example, it is used to send a mail and it is having the variable names that is defined to our variable window(see below fig) 

Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net



Public 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()

        Dim vars As Variables
        Dim myHtmlMessage As MailMessage
        Dim mySmtpClient As SmtpClient
        Dim Message As String = "This is Test Mail"
        Try


            myHtmlMessage = New MailMessage()

            Dim FromAddress As String
            Dts.VariableDispenser.LockOneForWrite("from", vars)
            FromAddress = vars("from").Value.ToString().Trim()
            vars.Unlock()

            Dim ToAddress As String
            Dts.VariableDispenser.LockOneForWrite("to", vars)
            ToAddress = vars("to").Value.ToString().Trim()
            vars.Unlock()

            Dim CCAddress As String
            Dts.VariableDispenser.LockOneForWrite("cc", vars)
            CCAddress = vars("cc").Value.ToString().Trim()
            vars.Unlock()

            Dim SmtpClient As String
            Dts.VariableDispenser.LockOneForWrite("smtpServer", vars)
            SmtpClient = vars("smtpServer").Value.ToString().Trim()
            vars.Unlock()


            Dim SmtpPort As String
            Dts.VariableDispenser.LockOneForWrite("smtpPort", vars)
            SmtpPort = vars("smtpPort").Value.ToString().Trim()
            vars.Unlock()


            myHtmlMessage.From = New MailAddress(FromAddress)

            Dim tt As String()
            If (ToAddress.ToString().Trim().Length > 0) Then
                If (ToAddress.IndexOf(";") > 0) Then
                    tt = ToAddress.Split(";")
                    Dim c As String
                    If (tt.Length > 0) Then
                        For Each c In tt
                            myHtmlMessage.To.Add(c)
                        Next
                    End If
                Else
                    myHtmlMessage.To.Add(ToAddress.ToString().Trim())
                End If
            End If

            Dim cc As String()
            If (CCAddress.ToString().Trim().Length > 0) Then
                If (CCAddress.IndexOf(";") > 0) Then
                    cc = CCAddress.Split(";")
                    Dim c As String
                    If (cc.Length > 0) Then
                        For Each c In cc
                            myHtmlMessage.CC.Add(c)
                        Next
                    End If
                Else
                    myHtmlMessage.CC.Add(CCAddress)
                End If
            End If

            Dim MailSubject As String = " Email scheduling process"
            Dim mailBody As String = "Hi, </BR>" + Message + "</BR> Thanks, </BR> XXX"

            myHtmlMessage.Subject = MailSubject
            myHtmlMessage.IsBodyHtml = True
            myHtmlMessage.Body = mailBody
            mySmtpClient = New SmtpClient(SmtpClient, SmtpPort)


            'Dim myCredentials As System.Net.NetworkCredential
            'myCredentials = New System.Net.NetworkCredential("nsuresh", "xxxx")

            'mySmtpClient.Credentials = myCredentials
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
            mySmtpClient.Send(myHtmlMessage)

            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
        End Try
    End Sub

End Class





Now save and execute we will get mail shortly.


05:15 | Category: | 0 comments