No SFTP Task Component in SSIS 2005/2008? No Problem!

  • mecurioJ

    SSC Rookie

    Points: 41

    Stan,

    That wasn't my intent. I think what you have is a good article, I just don't want to come across as pithy or superior. What if we combine our content? We are both talking about the same topic. shoot me an email and let's talk about it.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    I wasn't being snarky either. I like what your way better than mine, but if I hadn't put my way out there, I would never have learned about yours.

    It's synergy!

  • Mat Culpepper

    SSC Enthusiast

    Points: 138

    If you don't want usernames and passwords, you can always use certificate based authentication.... Of course then, the issue with the key on the server changing becomes that much harder because now you have a client key to manage as well. I think that in a better world, this type of integration would be managed by a system that has first class support for SFTP and would allow this type of connectivity without worries such as the username and password being stored in a file in clear text... just my 1.4 cents worth.

  • vonda.ackerman

    SSC Journeyman

    Points: 75

    This works great when I use it to send 1 file at a time. How can I edit it to make it loop through a list of files in a folder and send them all by the same sftp?

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    Basically, you would just get the list of files using this code:

    http://www.thescarms.com/dotnet/listfiles.aspx

    Imports System.IO

    Dim strFileSize As String = ""

    Dim di As New IO.DirectoryInfo("C:\temp")

    Dim aryFi As IO.FileInfo() = di.GetFiles("*.txt")

    Dim fi As IO.FileInfo

    For Each fi In aryFi

    strFileSize = (Math.Round(fi.Length / 1024)).ToString()

    Console.WriteLine("File Name: {0}", fi.Name)

    Console.WriteLine("File Full Name: {0}", fi.FullName)

    Console.WriteLine("File Size (KB): {0}", strFileSize )

    Console.WriteLine("File Extension: {0}", fi.Extension)

    Console.WriteLine("Last Accessed: {0}", fi.LastAccessTime)

    Console.WriteLine("Read Only: {0}", (fi.Attributes.ReadOnly = True).ToString)

    Next

    Instead of writing to the console, write to a 2-dimensional variable, then you just loop through them.

    If this is not enough to get you there, let me know an I will try to write some custom code, but it might take a few days to find the time.

  • vonda.ackerman

    SSC Journeyman

    Points: 75

    Can we use a wildcard(*) in the "SourceFilePath" in this code that would pick up all files with a .TXT file extenstion?

    Dts.Variables("SourceFilePath").Value = "c:\File\*.TXT"

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    Doesn't work with wild cards. You're not in DOS, your using VB.NET SYSTEM.IO objects.

    I went through all this myself. There is no other way.

  • mecurioJ

    SSC Rookie

    Points: 41

    Actually, it's a limitation of the FTP stack. Designed that way and won't change. The System.IO.DirectoryInfo.GetFiles method will allow wild card selections such as Vonda suggested. This method has existed since the .Net 1.1 framework.

    I would use String.Format to create and append files to the SFTP Command string. Once you execute the command, all of the files would be moved based on what the value of the command string is.

    J.

  • vonda.ackerman

    SSC Journeyman

    Points: 75

    could you give me a quick example of what you are suggesting?

  • mecurioJ

    SSC Rookie

    Points: 41

    Depends on which example you are using...Stan's example or mine from previously in the Discussion.

    the answer I am suggesting relates to my code. If you are working with Stan's example, I would recommend looking at samples from the psftp site (which I don't know off the top of my head) to see if there is a way to do it in the batch file. A put command moves a single file. so if we issue several put commands, it stands to reason we can move multiple files that way.

    FTP is a "one file at a time" method of communication between servers. that's why you just can't say "C:\*.txt" and get the entire directory to move.

  • vonda.ackerman

    SSC Journeyman

    Points: 75

    I am new to this, but what i am looking for is a way to get the files, then loop through them using the same sftp code as listed that will send these files one and a time. Is that possible?

  • mecurioJ

    SSC Rookie

    Points: 41

    New to SSIS?

  • vonda.ackerman

    SSC Journeyman

    Points: 75

    VB.NET

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    I finally got a chance to write some code. I had to change the global variable "SourceFilePath" to "SourceDirectory." You can download a revised version of the SSIS package here: http://www.box.net/shared/t0dmsjrtl0

    Imports System

    Imports System.IO

    Imports System.Data

    Imports System.Math

    Imports System.Windows.Forms

    Imports System.Diagnostics.Process

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

    'Load local variables with values from global variables

    Dim PathToBatch_bat As String = Dts.Variables("PathToBatch_bat").Value.ToString

    Dim PathToPsftp_exe As String = Dts.Variables("PathToPsftp_exe").Value.ToString

    Dim PathToScript_sc As String = Dts.Variables("PathToScript_sc").Value.ToString

    Dim ServerName As String = Dts.Variables("ServerName").Value.ToString

    Dim ServerUserName As String = Dts.Variables("ServerUserName").Value.ToString

    Dim ServerPassword As String = Dts.Variables("ServerPassword").Value.ToString

    Dim ServerSubdirectory As String = Dts.Variables("ServerSubdirectory").Value.ToString

    Dim SourceSubdirectory As String = Dts.Variables("SourceSubdirectory").Value.ToString

    'Create dynamic content of script file with local variable values and names of files in source subdirectory

    Dim commands As String

    Dim di As New IO.DirectoryInfo(SourceSubdirectory)

    Dim aryFi As IO.FileInfo() = di.GetFiles("*.*")

    Dim fi As IO.FileInfo

    commands += "cd " & ServerSubdirectory & Chr(13) & Chr(10)

    For Each fi In aryFi

    commands += "put " & SourceSubdirectory + fi.Name & Chr(13) & Chr(10)

    Next

    commands += "quit"

    Dim button As DialogResult = MessageBox.Show(commands, "Files in Subdirectory", MessageBoxButtons.OK)

    'Write script file

    Dim oFile As System.IO.File

    Dim oWrite As System.IO.StreamWriter

    oWrite = oFile.CreateText(PathToScript_sc)

    oWrite.WriteLine(commands)

    oWrite.Close()

    'Create command line to run psftp in this format: psftp.exe sftp.server.domain -l username -p password -b script_file_path

    Dim command_line As String = PathToPsftp_exe & " " & ServerName & " -l " & ServerUserName & " -pw " & ServerPassword & " -b " & PathToScript_sc

    'Write batch file

    oWrite = oFile.CreateText(PathToBatch_bat)

    oWrite.WriteLine(command_line)

    oWrite.Close()

    'Run batch file as system process

    Dim startInfo As System.Diagnostics.ProcessStartInfo

    Dim pStart As New System.Diagnostics.Process

    startInfo = New System.Diagnostics.ProcessStartInfo(PathToBatch_bat)

    pStart.StartInfo = startInfo

    pStart.Start()

    pStart.WaitForExit()

    'Delete script and batch files

    Dim DeleteFileInfo As New FileInfo(PathToScript_sc)

    DeleteFileInfo.Delete()

    DeleteFileInfo = New FileInfo(PathToBatch_bat)

    DeleteFileInfo.Delete()

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    I tried to post this to you the first time, but it went to Grasshopper, I think. I am going to try again.

    I finally got a chance to write some code. I had to change the global variable "SourceFilePath" to "SourceDirectory." You can download a revised version of the SSIS package here: http://www.box.net/shared/t0dmsjrtl0

    Imports System

    Imports System.IO

    Imports System.Data

    Imports System.Math

    Imports System.Windows.Forms

    Imports System.Diagnostics.Process

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

    'Load local variables with values from global variables

    Dim PathToBatch_bat As String = Dts.Variables("PathToBatch_bat").Value.ToString

    Dim PathToPsftp_exe As String = Dts.Variables("PathToPsftp_exe").Value.ToString

    Dim PathToScript_sc As String = Dts.Variables("PathToScript_sc").Value.ToString

    Dim ServerName As String = Dts.Variables("ServerName").Value.ToString

    Dim ServerUserName As String = Dts.Variables("ServerUserName").Value.ToString

    Dim ServerPassword As String = Dts.Variables("ServerPassword").Value.ToString

    Dim ServerSubdirectory As String = Dts.Variables("ServerSubdirectory").Value.ToString

    Dim SourceSubdirectory As String = Dts.Variables("SourceSubdirectory").Value.ToString

    'Create dynamic content of script file with local variable values and names of files in source subdirectory

    Dim commands As String

    Dim di As New IO.DirectoryInfo(SourceSubdirectory)

    Dim aryFi As IO.FileInfo() = di.GetFiles("*.*")

    Dim fi As IO.FileInfo

    commands += "cd " & ServerSubdirectory & Chr(13) & Chr(10)

    For Each fi In aryFi

    commands += "put " & SourceSubdirectory + fi.Name & Chr(13) & Chr(10)

    Next

    commands += "quit"

    Dim button As DialogResult = MessageBox.Show(commands, "Files in Subdirectory", MessageBoxButtons.OK)

    'Write script file

    Dim oFile As System.IO.File

    Dim oWrite As System.IO.StreamWriter

    oWrite = oFile.CreateText(PathToScript_sc)

    oWrite.WriteLine(commands)

    oWrite.Close()

    'Create command line to run psftp in this format: psftp.exe sftp.server.domain -l username -p password -b script_file_path

    Dim command_line As String = PathToPsftp_exe & " " & ServerName & " -l " & ServerUserName & " -pw " & ServerPassword & " -b " & PathToScript_sc

    'Write batch file

    oWrite = oFile.CreateText(PathToBatch_bat)

    oWrite.WriteLine(command_line)

    oWrite.Close()

    'Run batch file as system process

    Dim startInfo As System.Diagnostics.ProcessStartInfo

    Dim pStart As New System.Diagnostics.Process

    startInfo = New System.Diagnostics.ProcessStartInfo(PathToBatch_bat)

    pStart.StartInfo = startInfo

    pStart.Start()

    pStart.WaitForExit()

    'Delete script and batch files

    Dim DeleteFileInfo As New FileInfo(PathToScript_sc)

    DeleteFileInfo.Delete()

    DeleteFileInfo = New FileInfo(PathToBatch_bat)

    DeleteFileInfo.Delete()

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

Viewing 15 posts - 16 through 30 (of 61 total)

You must be logged in to reply to this topic. Login to reply