FTP basics 2

[donutman]

I'm totally unfamiliar with using FTP inside or outside of MSSQL Server. I see that DTS can automate an FTP task, but I don't see how I can do exactly what I want. It doesn't look like the file name can be based upon GetDate().
I will need to download a CSV file from a remote FTP site on a daily basis. It will have a file name that includes yymmdd in it's name. The data in the file consists of the daily updates to an existing table. What is the best way to automate this task. Please provide detail as I'm unfamiliar with all except how to update the table once I have the file in a secondary table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JohnDTampaBay]

I am thinking you will need three tasks in a DTS package.
1 - Dynamic Properties Task that updates the filename for the next two tasks.
2 - FTP Task to download the text file
3 - Data Pump Task to import once downloaded

Don't worry about the dynamic part just yet. Pick a filename that you want to use for development. Set up an FTP Task download that text file. Run that task so you have a downloaded file work with.

Next, set up a Data Pump Task to import your downloaded text file into SQL Server. You will have to create connections for the source text file and the destination SQL database. If this part is confusing, use the Import Wizard to import the text file, save the package and check out what the Wizard builds. Once you have a Data Pump Task defined, run the task and verify that everything imported okay.

Almost there! Add workflow: select the FTP Task, hold down Ctrl and select the text file (source) connection. Choose Workflow, then On Success from the menu. This tells DTS that after the FTP Task succeeds, move on to the Data Pump Task.

Now add a Dynamic Properties Task. You will add two changes to the dialog box that opens. First, click Add and drill down into Tasks to find the FTP Task. Note the information that is currently in the SourceFilename property. Double-click on the SourceFilename property.

In the Add/Edit Assignment dialog box that opens, choose Query as the source and choose your SQL connection. In the Query box, write a SELECT statement returns the filename you want using GetDate(). Use the Refresh button to preview the value of the SELECT statement. When satisfied, click OK until you return to the Dynamic Properies dialog box.

Add another and drill down into Connections to find the Text file connection. Double-click on the DataSource property. Repeat the Query steps as above, creating the filename. When complete, click OK several times.

Repeat the workflow steps from above to tie the Dynamic Properties Task to FTP Task. This way the Dynamic Properties will update the FTP filename and import datasource, then the FTP will download the file, then the Data Pump will import the table.

This may seem a little tricky, but it will make more sense as you go. Good luck! If you have more questions, post away!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[donutman]

Thanks John, nicely written. I'll give it a go...and I may be back.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[nigelrivett]

Have a look at

http://www.nigelrivett.net/s_ftp_PutFile.html

Which does the ftp from t-sql. On the same site is a getdir script. Getfile... are similar. It works by creating a command file to send to ftp so can be tested using the command line.

======================================
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.