Solved

SSIS Ftp Script Task using C#

Posted on 2010-09-14
12
2,698 Views
Last Modified: 2013-12-16
have a requirement in SSIS to connect to a remote ftp site and  move  files from my local machine to remote ftp file  and then move the local files to a local archives folder. The file names will be different each time.

How to script that using SSIS script task  and C#

Thanks
0
Comment
Question by:Fatehbourghoud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33675945
you don't need scripting for this case,
you can use FTP Task in ssis , to move files from local to ftp address,
and use File system Task to move local files to archives folder.
you should use Foreach Loop Container to loop through source local directory and move files appropriately. don't worry about different file names, foreach loop will loop and get file names iteratively, and you can fill result of this into variables, and use variables in file system task and ftp task.

there are lots of details in this solution.
please let me know where you need more details?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33676236
I'm explain in details solution:

1-create a ssis package
2- add a variable of type string, scope package, name it SourceFile
3- add a foreach loop container
double click on it
set enumerator as Foreach File enumerator
set folder with source folder on your local
set Retrieve file name as Fully qualified

go to variable mapping tab,
set varaible as User::SourceFile , and Index as 0

4- add a FTP task inside foreach loop container
set ftp connection,
in file transfer tab, set IsLocalPathVariable as True,
and set LocalVariable as User::SourceFile
set operation as Send files
set remote path appropriately to a remote folder as you want

5- add a File System Task inside foreach loop container
set operation as copy file
set destination folder as you want
set IsSourcePathVariable as true,
set SourceVariable as User::SourceFile

That's all,
now run the package.


0
 

Author Comment

by:Fatehbourghoud
ID: 33676239
Hi Raza ;
 how to  fill result of this into variables ?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Fatehbourghoud
ID: 33676266
ok sorry I just see your second post , I will try to follow the steps and let you know

Thanks
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33676289
let me know if you have problem on implementation
0
 

Author Comment

by:Fatehbourghoud
ID: 33676582
Hi raza;

I followed the steps , the FTP tasks works fine , but I have an error as follow :

variable "SourceFile" is used as source  or distination and is empty

FordistinationConnection I create a new connection as Existing folder and gave it my Archives Folder .

Any thing missing or not Correct ?

0
 

Author Comment

by:Fatehbourghoud
ID: 33676598
sorry the error is whent I Try to create a file system task to move the files to the Archive folder
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33676699
you need to set a default value for SourceFile variable
this default value should be a valid physical value exactly.
don't worry about this default value, this is just for SSIS validation phase, and will not act on runtime.
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 500 total points
ID: 33676705
sorry, I meant valid physical file address
0
 

Author Comment

by:Fatehbourghoud
ID: 33676804
Thank's raza , it's works fine now
0
 

Author Closing Comment

by:Fatehbourghoud
ID: 33676819
Reza_rad Is verry the best Expert
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33676849
Glad to help,
Regards,
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question