December 5, 2011 at 8:25 am
For our web I am having to down load a file off a ftp site.
The file name can change from RORD0001, RORD0002 e.t.c
I need to down load the file and then delete off the ftp site.
Down loading the file is not a problem but the issue I have is to delete just the one file. For example if I was downloading RORD* then deleting RORD* it could delete files I have not yet download if they we dropped onto the site whilst the others were downloading. Therefore I need to take the name of the file I have down loaded and delete that one file.
Once the file has been deleted eg RORD0001 then they drop another file called RORD001
There is probably a better way of doing it
Sorry it is so muddly and confusing. If you are not clear on something please say.
Glad of any help
December 5, 2011 at 8:36 am
Try SSIS. You will find all necessary tasks there you might need to perform on your files.
December 5, 2011 at 8:38 am
Is this using the Export and Import Tool?
December 5, 2011 at 8:46 am
IIRC, Export Import tool doesn't support FTP. You need to create a new SSIS package in BIDS.
December 5, 2011 at 9:33 am
Thanks. I have been having a look at it. Never realised there was this tool.
Do you have an example of what you have done similar to what I am trying to do.
What sequence fo tasks would you set up
December 5, 2011 at 9:38 am
SSIS Tutorial: Creating a Simple ETL Package
December 6, 2011 at 2:11 am
kyle.doouss (12/5/2011)
Thanks. I have been having a look at it. Never realised there was this tool.Do you have an example of what you have done similar to what I am trying to do.
What sequence fo tasks would you set up
You are looking on FTP task here is the steps to follow:
Open BIDS then
1. Drag Data Flow Task on the Control Flow tab.
2. Double click on the Data Flow Task.
3. Drag Ole Db Source on the Data Flow tab.
4. Double click on it.
5. Click on New button and add connection to database table.
6. Click on Columns list box item and select columns from the table.
7. Click on OK button.
8. Drag Flat File Destination on the Data Flow tab.
9. Drag green arrow from it on the Flat File Destination component.
10. Double click on it.
11. Click on new button and select destination file.
12. Click on mappings list box item and select column mappings between database columns and file columns.
13. Click on Control Flow tab.
14. Drag FTP task on it.
15. Drag green arrow from Data Flow Task and place on FTP task.
16. Double click on FTP task and click on FTP connection and enter valid data for it
17. Click on File transfer list box item and enter remote and local paths.
15. press F5 and check if file is on the FTP site.
After that you can set u credentials and connetion...
Need help? Help us help you.
December 6, 2011 at 2:14 am
Thanks. I will get started!!
December 6, 2011 at 2:18 am
WC...If u face any issues post it 🙂
Need help? Help us help you.
December 6, 2011 at 3:05 am
I faced now issues regards exporting a view to a flat file and sending it to the FTP site. Thanks for your clear instructions it worked first time!!
I currently have set this up as stored procedures which is working.
The issue I have is downloading from the ftp site as mention on my first post.
Do you have any ideas how I will cope with this? Glad of any help. Please ask questions if you don't understand what I am trying to do.
December 6, 2011 at 3:11 am
kyle.doouss (12/6/2011)
I faced now issues regards exporting a view to a flat file and sending it to the FTP site. Thanks for your clear instructions it worked first time!!I currently have set this up as stored procedures which is working.
The issue I have is downloading from the ftp site as mention on my first post.
Do you have any ideas how I will cope with this? Glad of any help. Please ask questions if you don't understand what I am trying to do.
If u dnt mine...Canu post an original or sample packge wt ur xactly trying to do 🙂
Need help? Help us help you.
December 6, 2011 at 3:18 am
Sorry I have not created a package yet.
Below is what I am trying to do.
1. The website sends a file to the ftp site (say tab delimited) This is name RORD0001, then RORD0002 for the next order.
2. I need to then down load that file to a folder on our server
3. Upload the file in a API table
4. Delete the file off the ftp site
Once the file RORD0001 has been deleted then the website will replace it with another file called RORD001.
If you have any questions please ask
December 6, 2011 at 10:57 pm
kyle.doouss (12/6/2011)
Sorry I have not created a package yet.Below is what I am trying to do.
1. The website sends a file to the ftp site (say tab delimited) This is name RORD0001, then RORD0002 for the next order.
2. I need to then down load that file to a folder on our server
3. Upload the file in a API table
4. Delete the file off the ftp site
Once the file RORD0001 has been deleted then the website will replace it with another file called RORD001.
If you have any questions please ask
Sry for late rply...If you want to delete that file(if exists in loaction) you can use the WMI Event watcher task to know more go through the below link:
http://msdn.microsoft.com/en-us/library/ms141130.aspx
I hope this will help you
Need help? Help us help you.
December 6, 2011 at 11:22 pm
I feel the database server should be left to do the core task of processing SQL and serving data to clients. Other activities wherever possible should be moved off to another box. SSIS surely does a great job of performing a variety of tasks but in future as load increases on your database server it can cause a performance problem.
In this case I would write a PowerShell script that does two activities:
1. Download the files from FTP and delete them
2. Use BCP (part of client tools) to load the file into the database
You can schedule this PowerShell script using Windows Task Scheduler or any enterprise scheduling software if you already have it in your environment.
Since PowerShell is a much more expressive programming language there is a lot more custom logic you can put in. You can take a call based on how big/loaded your environment is.
December 7, 2011 at 1:37 am
Thanks for the guidance.
How would you use the Bcp to put specific fields into specific fields in the API table?
Does it work with XML documents?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply