Scenario:
Vendor put the file with date in one of the folder for us to download. After downloading the file we want to move those files to the Archive Folder that exists on FTP Server.Solution:
To handle this situation, we will be using the Script Task in SSIS Package as FTP Task capabilities are limited to perform this task.
We will learn
- How to Create an SSIS Package from scratch
- How to Create Variables to save FTP Server, Remote Folder , Local Folder Paths
- How to use variables in Script Task in SSIS Package
- How to Download the file by using Script Task and move to different folder ( Archive) on FTP Server
VarFileName =
@[User::FileName]+Replace(Substring((DT_WSTR,30)GETDATE(),1,10),"-","")+".txt"
ArchFilePath=
"../Archive/"+ @[User::FileName] +Replace(Substring((DT_WSTR,30)GETDATE(),1,10),"-","")+".txt"
Script used in Script Task to Move file from one folder to another folder on FTP Server
public void Main()
{ string UserName; string Password; string LocalFolder; string FileName; string FTPFileFullPath; string ArchFilePath; UserName = Dts.Variables["User::UserName"].Value.ToString(); Password = Dts.Variables["User::Password"].Value.ToString(); LocalFolder = Dts.Variables["User::LocalFolder"].Value.ToString(); FTPFileFullPath = Dts.Variables["User::FTPServer"].Value.ToString() + Dts.Variables["User::RemoteFolder"].Value.ToString() + Dts.Variables["User::VarFileName"].Value.ToString(); FileName = Dts.Variables["User::VarFileName"].Value.ToString(); ArchFilePath=Dts.Variables["User::ArchFilePath"].Value.ToString(); //Download the file to local folder WebClient Webrequest = new WebClient(); Webrequest.Credentials = new NetworkCredential(UserName, Password); MessageBox.Show(FTPFileFullPath); byte[] newFileData = Webrequest.DownloadData(FTPFileFullPath); File.WriteAllBytes(LocalFolder+FileName, newFileData); //Move the File to Archive Folder FtpWebRequest ftpRequest = (FtpWebRequest)WebRequest.Create(FTPFileFullPath); ftpRequest.Credentials = new NetworkCredential(UserName, Password); ftpRequest.Method = WebRequestMethods.Ftp.Rename; ftpRequest.RenameTo = ArchFilePath; FtpWebResponse ftpResp = (FtpWebResponse)ftpRequest.GetResponse(); // TODO: Add your code here Dts.TaskResult = (int)ScriptResults.Success; }
Move file from One folder to Another Folder on FTP Server in Script Task in SSIS Package
Related Posts / Videos on FTP Task / Script Task
- FTP Task - How to Upload Single File to FTP Server from Local Folder in SSIS Package
- FTP Task - How to Download Single File from FTP Server to Local Folder in SSIS Package
- FTP Task - How to Upload Multiple Files from Local Folder To FTP Server Folder in SSIS Package
- FTP Task - How to Download All the files from FTP Server Folder to Local Folder in SSIS Package
- FTP Task - Filter Files by using WildCard in FTP Task in SSIS Package for downloading
- FTP Task - Delete Specific Files or All Files from FTP Server Folder by using SSIS Package
- FTP Task - Download Only Current Day Files from FTP Server by using FTP Task in SSIS Package
- FTP Task - Create A Directory Folder For Each Day and Load Files on FTP Server by using SSIS Package
- Get File Names from FTP Server and Save to SQL Server Table in SSIS Package by using Script Task
- FTP Task and Script Task - How to Avoid FTP Task error when no file found on FTP Server
- FTP Task and Script Task - How to Delete Folder with Files from FTP Server by using SSIS Package
- FTP Task and Script Task - Download a File from FTP Site and Delete after Download in SSIS Package
- FTP Task and Script Task - Sync Local Folder to FTP Server Folder without Upload Existing Files by SSIS Package
- Sync FTP Folder to Local Folder without Downloading Existing Files by using Script Task in SSIS Package
- FTP Task/Script Task - Rename File on FTP Server After Downloading in SSIS Package
- FTP Task - Create Local Folder with Date on Daily basis and load files from FTP Folder in SSIS Package
- FTP Task - How to Save Password,User Name,Ftp Server Name as variables/Parameters in SSIS Package to make SSIS Package Dynamic to run in SIT, UAT, QA and Prod.
hello i'm using sql server 2012 and I have .net and c# 2010. m using your script from your blogs its showing error any sugessation why its showing error hopping to hear soon
ReplyDeletePlease your script and application turn off you can to send me the link for your package brother
ReplyDeleteThanks for sharing your knowledge to install & crack the Time Tables, but you need to update
ReplyDeleteit now. because there is a 2022 version available now.
key4pc.com
folder-lock-crack
Is this a paid topic or do you change it yourself?
ReplyDeleteHowever, stopping by with great quality writing, it's hard to see any good blog today.
http://licensedinfo.com/
Folder Lock Crack
NextUp TextAloud Crack
Active File Recovery crack
Symantec Endpoint Protection crack
ChrisPC VideoTube Downloader Pro'