Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create FTP Connection through script task in SSIS 2008


Create FTP Connection through script task in SSIS 2008

Author
Message
playb_g
playb_g
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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!!



PurpleLady
PurpleLady
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4797 Visits: 969
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
playb_g
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 39
Thanks a lot... Problem resolved.



swetha859
swetha859
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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;
}

}
}
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search