I am fairly new to the SQL programming world, but from what I have read
in various news groups and sources, bcp is the fastest way of getting
data from a positional flat file into a sql table. I have implemented
the following solution using bcp and am curious if there is another
method I am missing that might be more effiecient or industry standard.

I receive positional flat files every 15 mins via ftp on my server. I
have a dts package the contains 2 vbscripts. The first step is to sort
all the files by create date and then parse the incoming file into 2
files based on the first 4 characters on each line. Once the 2 files
are created, the second script bcp imports each file into their
respective tables.

I thought about using Bulk Insert from DTS within SQL server, but from
what I can gather, it expects to import the same filename over and over.
The only way I could think to get multiple files bulk imported and
sorted by create date (in order to insert FIFO) was to use BCP. Are
there any other suggestions by the experts out there? Thanks for any
suggestions and the logic check.

Michael





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You can dynamically set the name of the file inside a loop through a
directory. Here is an example of a loop. Change the datapump task to a
Bulk Insert task.

Look also at the TSQL command BULK INSERT

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Michael Baker" <michael_baker (AT) advantexmail (DOT) net> wrote