Create FTP Connection through script task in SSIS 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Create FTP Connection through script task in...

Create FTP Connection through script task in SSIS 2008

Rate Topic

Display Mode

Topic Options

Author

Message

playb_g

Posted Monday, March 22, 2010 6:52 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 7:46 PM
Points: 11, Visits: 39

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

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"
'

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

ftp.Close()

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

End Class

Thanks in Advance!!

Post #887335

PurpleLady

Posted Wednesday, April 07, 2010 11:24 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:26 AM
Points: 3,598, Visits: 548

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/

Post #898797

playb_g

Posted Sunday, April 11, 2010 12:53 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 7:46 PM
Points: 11, Visits: 39

Thanks a lot... Problem resolved.

Post #901195

swetha859

Posted Monday, October 29, 2012 8:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 1:00 PM
Points: 1, Visits: 8

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

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.

ftp.ReceiveFiles(files, "", true, false);
// the True makes it overwrite existing file and False is saying that it is not transferring ASCII

ftp.Close();
}

catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}

}
}

Post #1378260

« Prev Topic | Next Topic »

Permissions