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

Question

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

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

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

mecurioJ SSC Rookie Points: 41 More actions · Answer 1

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 SSChampion Points: 10062 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 SSChampion Points: 10062 More actions · Answer 5

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)

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 More actions · Answer 6

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 SSChampion Points: 10062 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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

mecurioJ SSC Rookie Points: 41 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

mecurioJ

SSC Rookie

Points: 41

June 17, 2010 at 4:26 pm

#1181870

New to SSIS?

vonda.ackerman SSC Journeyman Points: 75 More actions · Answer 13

vonda.ackerman

SSC Journeyman

Points: 75

June 18, 2010 at 6:28 am

#1182072

VB.NET

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 14

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)

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 SSChampion Points: 10062 More actions · Answer 15

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