Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create FTP Connection through script task in SSIS 2008 Expand / Collapse
Author
Message
Posted Monday, March 22, 2010 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, April 7, 2010 11:24 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:27 AM
Points: 4,011, Visits: 715
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
Posted Sunday, April 11, 2010 12:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 7:46 PM
Points: 11, Visits: 39
Thanks a lot... Problem resolved.


Post #901195
Posted Monday, October 29, 2012 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse