Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Charoniv
Starting Member
21 Posts |
 Posted - 2007-01-02 : 10:28:45
|
| Hello everyone - wonder if you can help.How do you process all files in an FTP directory - similar to the for each loop for files.I need to be able to download each file and then move it to an archive folder on the ftp site.Might also want to do some things in between.I have a feeling it means getting the directory listing into a recordset or variable then enumerating that.Sounds like a common requirement and would be quite easy by other means. Can't help feeling that SSIS has built in tasks to do it. |
|
|
tacket
Starting Member
47 Posts |
Posted - 2007-01-23 : 17:52:51
|
| I see two tasks here which can easily be done in SSIS. I can't believe I said easily. Anyway, you'll need:1. For each Container2. Data flow task3. File System task4. some sort of flow precendence.. depending on whether or not you want the next task to run if the first one fails etc. (the little arrow thingy)5. variable set up as a stringCreate a variable like "fileName" as a string.I'm assuming you are wanting to load this data into SQL (you are saying "process" which is very generic). I'll assume you want to load the files one-by-one, record-by-record into a SQL table. Drag and drop the for each container into the control flow. Double-click on for each container and go to the "Collection" tab. You'll see Enumerator, to the right you'll see a drop down. Click on "Foreach File Enumerator". Now, you'll see Folder: and Files: appear. Browse to your directory in the Folder: option. In the files option you can set a name like *.dat or something to specify which files you want. You should be able to figure that out pretty easy through trial and error. You'll next need to map the variable in the For each container to be the filename. You can do this in the "Variable Mappings" section. What ever filename you have in your collection (ie. the Folder: you specified) will then be stored in the variable.Then drag over the data flow task and put it "inside" the for each loop container. You can then double-click in the data-flow and set up a "flat file source" and you'll need to create a connection for it. The connection can be like a "control file" or something that has the way your data will be inputted. You'll need to create the control file. After you have the flat file source you'll just need a "SQL server Destination" or whatever you want. You'll need to then map the columns from the file to the SQL table.Try that out and let me know if you have any problems/questions. If you get that far I'll post again on how to move the files in a different directory. That should be easy. You'll just need a precendence flow and the file system task. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-23 : 18:40:31
|
| You can't process all files in an ftp folder (well you could get a list in a script task, populate a variable and enumerate than in a for each loop but that's probably more complicated than you need).You can do an mget to a local folder then process all the files in that local folder - ftp task receive with a filemaskSet the ftp get task to overwrite any that are there.Inside the for each file loop you will need to move the ftp file to the archive folder - not possible with an ftp task but easy with a script task:http://www.simple-talk.com/sql/sql-server-2005/quick-tip-performing-an-ftp-rename/The last piece is that you need to set the password for the ftp task - the script uses a variable and you don't want to set it twice (you can't read it from the ftp task as it;s write only).To set the password in the ftp task use another script task with a setvalue for the pssword.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
SSIS processes all files in a FTP folder