using batch file to download flat file from ftp server so can bulkcopy into database

[jcrosby]

I use RUMBA and write FOCUS programs to pull data from our mainframe so I can then import the flat file into our Data Warehouse. I'm trying to eliviate some steps here and want to include the downloading of the flat file from the FTP Server to my server where I can then continue with the rest of the batch file and bulkcopy the data into my Data Warehouse. I can't figure out what code to use in the batch file to do that though. Can anyone help me please? I need help with the batch code to ftp the file to my server.

Thanks!
Jennifer

[Kurt]

Hi Jennifer,

I sounds like what you need is command file to call ftp and bcp to load your data. The command file (just a text file with a .cmd file extension) would look something like this:

REM -- Copy the flat file ---
FTP MYFILE.TXT ....
REM -- NOW BCP the fole into SQL Server
BCP SQlServerName.DBName.dbo.tablename in MYFILE.txt

If your SQL Server's server has access to the server on which the file is
located you could eliminate the FTP step and just issue a BCP.

REM -- Load the flat file
BCP SQLServerName.DBname.dbo.tablename in \RUMBASERVERFlatfilesMyfile.txt

ALSO

You can now perform bulk copy operations from T-SQL. Check out the Bulk Insert Operation in T-SQL (Books online).

The above code is psuedo-code and won't work. You will need to figure out
the syntax for both FTP, BCP and the Bulk Insert command.

Depending on the flat-file layout, you might have to create a format file (it's not hard to do) so SQL Server will not which columns are which.

Hope this helps.

------------
jcrosby at 11/6/01 6:52:43 PM


I use RUMBA and write FOCUS programs to pull data from our mainframe so I can then import the flat file into our Data Warehouse. I'm trying to eliviate some steps here and want to include the downloading of the flat file from the FTP Server to my server where I can then continue with the rest of the batch file and bulkcopy the data into my Data Warehouse. I can't figure out what code to use in the batch file to do that though. Can anyone help me please? I need help with the batch code to ftp the file to my server.

Thanks!
Jennifer