I have to write a job to bring in a text file into a sql database.

We have a ftp server set up and a place to read the file from.

I've been looking at this site:

http://www.sqldts.com/default.aspx?292

trying to get some ideas as to how to do this piece of a project.

What I need to do is change how we're now doing this process (I receive
an email, rename the file, then load into a folder then run sql dts job
manually) to get it into our existing table. I want to automate the
process, so it'll look out in this folder for the file, use it if it's
there (or skip out of the job if it isn't).

The filename I know should always be named: yyyymmdd.CUB

It needs to be renamed into a file "Current_coop.txt" for input into my
existing dts job.

However, I need to retain the date that is in the filename
(yyyymmdd.cub), and put it into a column on the table (an "as of" date
field).

Any ideas as to how to do this? The link above sorta-kinda does
something like that, but I'm not well-versed enough in using the
filesystemobject in DTS to know really what I'm doing.

Any help, ideas appreciated.

BC

Hi BC,

"Blasting Cap" wrote:
there are different ways to ahdle this, but before telling you all ways, I
would like to understand the hole scenario.
Do you plan to mirror something like daily logfiles from a ftp server?
Could it be useful to be able to import multiple files for several days?
You don't need to rename the file for DTS import - there are other ways -
just as a hint to free your mind to clarify what you realy want ;-)

Helge

Helge C. Rutz wrote:

What this is is taking a text file from our system (not the one I am
working on), having it FTP'd to an outside source, who'd then take that
file & manipulate & update it on their end, then ftp the updated one
back to me. Since each load of it would be a direct replacement of the
table that's out there now, I see no reason that it'd need to have the
ability to accept multiple files. So to answer your first question - it
is similar to a daily logfile, but not exactly the same.


Question #2 (multiple files for several days) - probably not. This is
anticipated to be sent like at least 1 time a week now, but during the
closing days of our business year, it would go to 2-3 times a week, up
to daily.

Thanks,

BC

Hi BC,

"Blasting Cap" wrote:
ok, when this is the scenario, you can do the following.
use the sqldts dtp task to get the file from your ftp server by using
wildcards - if you can't use this extension we have to find a different way
to deal with the "unknown" filename for ftp transfer.
http://www.sqldts.com/default.aspx?302
Is there always only one file on that server, where the filename always
tell the last date changed?
You can get the name of the copied file by the ftp task as global variable
as well.
If the file is the same as the last imported - you should be able to get
this information from your DB - or nothing there, then stop the import.
Otherwise copy the filename to a single global variable with type string,
change the source file for the Text file connection to the actual filename
and procede.
http://www.sqldts.com/default.aspx?200
After import store the date of the filename in your DB by splitting the
name and use the first part directly as datetime insert string, using a
parameterized sql task.
Delete the file as described with the filesystem objects.
For description of the ActiveX vbs, ojects, and so on go to the msdn
library or follow the link on the sqldts page you first noted.

HTH
Helge

On Mon, 27 Jun 2005 11:46:44 -0400, Blasting Cap wrote:

How I handled this (just finished today, in fact!)

My own situation involved downloading four files, each with a name based on
the current month (yyyymm) plus a stub describing the purpose, and
inserting them into two tables. Platform is Windows 2000.

I decided to delegate the ftping and file logic to a batch file, using wget
to do the ftping. First, we assign the date to an environment variable:

SET YYYYMM=%DATE:~10,4%%DATE:~4,2%

Next, wget is used to do the ftp'ing. It automatically takes care of only
downloading newer files for me:

rem -- the following is all on one line
wget 2>done -N -nv --passive-ftp -P e:\path
ftp://ftp.thesite.com/%YYYYMM%{foo,bar}{bletch,quux}.txt

Next, I inspect the stderr written by wget to see if any files were
downloaded

grep -q "in 0 files" done
if not errorlevel 1 goto skip

Now, I build an ini file with the new filenames:

echo >files.ini [Filenames]
echo >>files.ini foobletch=e:\path\%YYYYMM%foobletch.txt
echo >>files.ini barbletch=e:\path\%YYYYMM%barbletch.txt
echo >>files.ini fooquux=e:\path\%YYYYMM%fooquux.txt
echo >>files.ini barquux=e:\path\%YYYYMM%barquux.txt

The first step of my DTS task will read these filenames from the ini file
and use them to set the datasource of each of 4 connections.

The remaining steps intersperse datapumps with sql, to bring about the
proper import. (foobletch and fooquux go to the foo table and may include
duplicates; barbletch and barquux go to the bar table and will not include
duplicates)


Meaning of the various wget options:
2>done write stderr to the file named "done"
-N only download files that are newer or don't already exist
-nv less verbose output
--passive-ftp use passive ftp
-P e:\path write output files to e:\path

ftp://ftp.thesite.com/%YYYYMM%{foo,bar}{bletch,quux}.txt
the lists inside braces {} will turn this one expression into four
URLs