For some reason, importing text files (Tab, CSV, etc) into Access seems
more stable than DTS into SQL. Many times I'll get date/time conflicts
or "delimiter not found" messages DTS'ing when an import or link into
Access works without a hitch.

I'm dealing with large files from external systems (millions of
customers) and need to be able to transport files of different names,
contents, etc, very quickly.

The easy thing would be to set as a link table in Access, but DTS
doesn't want to import an Access link table. So I tried creating a
view for the link table and DTS attempts to import it, but it gives me
a "specification not found" message (the file specification for the
link table). The other option of course is to do a make table in
Access, but that's a lot of bandwidth for redundant data and very time
consuming.

So I'm thinking of giving UDL a whirl. If I could create a UDL file
with the definition of each file akin to doing a link table in Access
or DTS of a text table, maybe it would be more stable and provide a
convenient/portable way to link files. I don't want to create a DSN
for every file as they are too many in number and I want to preserve
the structure, so UDL seems a way to go.

Problem is, I don't find a way to create a UDL file for text data. Is
this possible to do? Otherwise, any of you DTS gurus have any
suggestions? I'm surprised that an end user app like Access is more
stable at importing than SQL Server!

Thanks

groups_mw (AT) yahoo (DOT) com wrote:
Access is not more stable than DTS, it just has a different parses. You
may find this better, but others may prefer the DTS. DTS follows the
strict logic for delimiters or fixed columns. If you have CSV and define
three columns, DTS will expect to find 2 commas then the line return. If
you only have one comma, then a line break DTS thinks the line break is
part of the data and keeps going onto the next line looking for the
comma. Access would say, ooh look there is a line delimiter, never mind
about the missing column, I'll just leave that blank. Let's start on a
new row of data.

So which is right? If you think Access, then why? Is it not possible
that you can have data with line break characters in? What is a line
break, this defined character or is it just a delimiter. Why not have a
column delimiter of comma and a line delimiter of X. What happens if you
want a CSV file that includes with a big free text field which can
include line breaks within the data? DTS allows different flexibility,
but is very strict. Two interpretations of the same logic really. I'd
argue DTS is correct and your files are not formatted correctly, but
being pragmatic it would be nice to have the best of both worlds.
Standards are great, but what is the standard!


Not sure what the UDL gives you over a normal text file connection in
DTS. You can change connection properties dynamically to handle changing
file names. Changing file structures would require a different
connection, and Data Pump.

You may like to try BCP or the Bulk Insert Task. These can be slightly
more forgiving, and the file format can be held in a format file, so
this can be changed easily.