Create FTP Connection through script task in SSIS 2008

Question

Post reply

Create FTP Connection through script task in SSIS 2008

playb_g

Old Hand

Points: 327
More actions
March 22, 2010 at 6:52 am

#386554

Hi All,
Please help. I'm new to SSIS 2008. I'm using the code below to FTP File,
Question :-
1) Seem that the connection is open even though the SSIS already stopped execute. The source file cant be amend/delete after FTP.
2) Any code can help to rename the destination file through below code or sub function.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
'
' Add your code here
Dim sSource, sTarget, sFileName As String
Dim YYYY, YY, MM, DD, HH, MI As String
YYYY = Now.Year
YY = Mid(YYYY, 3, 2)
MM = Now.Month
DD = Now.Day
HH = Now.Hour
MI = Now.Minute
If Len(DD) = 1 Then
DD = "0" & DD
End If
If Len(MM) = 1 Then
MM = "0" & MM
End If
If Len(HH) = 1 Then
HH = "0" & HH
End If
If Len(MI) = 1 Then
MI = "0" & MI
End If
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftpserver1")
cm.Properties("ServerUserName").SetValue(cm, "***")
cm.Properties("ServerPassword").SetValue(cm, "***")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'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
http://ftp.Connect()
'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) = "LocalServerpath"
'ftp the file
'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.
sFileName = Dts.Variables("User::EXPORT_REPORT_01").Value & MM & DD & YY
files(0) = sFileName & ".dat"
sTarget = sFileName & HH & MI & ".dat"
'
http://ftp.SendFiles(files, "Invoice_Data_File\", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII
http://ftp.Close()
Catch ex As Exception
Dts.TaskResult = DTSExecResult.Failure
End Try
Dts.TaskResult = DTSExecResult.Success
End Sub
End Class
Thanks in Advance!!

Viewing 4 posts - 1 through 3 (of 3 total)

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

PurpleLady SSCertifiable Points: 7819 More actions · Answer 1

I'm looking to setup a similar process and found this script that might be helpful for you

http://www.simple-talk.com/sql/ssis/quick-tip-performing-an-ftp-rename-in-a-ssis-script-task/

playb_g Old Hand Points: 327 More actions · Answer 2

playb_g

Old Hand

Points: 327

April 11, 2010 at 12:53 am

#1149685

Thanks a lot... Problem resolved.

swetha859 SSC Enthusiast Points: 101 More actions · Answer 3

I am i doing any mistake hear. I don't know remote path and my file name is pmain.rs.out.x085.ae i have to see the data in that file. when i run the package it is successfully but I am not seeing that file can any one please help me out.

public void Main()

{

try

{

// TODO: Add your code here

ConnectionManager cm = Dts.Connections.Add("FTP");

//Set the properties like username & password

//cm.Properties["ServerName"].SetValue(cm, "10.32.130.28"/*Dts.Variables["ftpDropLoc"].Value.ToString()*/);

cm.Properties["ServerName"].SetValue(cm, "facs.state.mi.us");

cm.Properties["ServerUserName"].SetValue(cm, "#085AE1");

cm.Properties["ServerPassword"].SetValue(cm, "MEDC$A12");

cm.Properties["ServerPort"].SetValue(cm, "21");

cm.Properties["Timeout"].SetValue(cm, "0");

//The 0 setting will make it not timeout

cm.Properties["ChunkSize"].SetValue(cm, "1000");

//1000 kb

cm.Properties["Retries"].SetValue(cm, "1");

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

FtpClientConnection ftp = new FtpClientConnection(cm.AcquireConnection(null));

//Connects to the ftp server

http://ftp.Connect();

//Build a array of all the file names that is going to be FTP'ed (in this case only one file)

string[] files = new string[] { @"C:\AMS_FTP_ETL\pmain.rs.out.x085.ae" };

//files[0] = @"C:\Report1.pdf";//Dts.Variables["fileDropLoc"].Value.ToString();

//ftp the file

//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.

http://ftp.ReceiveFiles(files, "", true, false);

// the True makes it overwrite existing file and False is saying that it is not transferring ASCII

http://ftp.Close();

}

catch

{

Dts.TaskResult = (int)ScriptResults.Failure;

}

Dts.TaskResult = (int)ScriptResults.Success;

}