February 27, 2004 at 2:26 am
Hi,
we have to load a file with two kinds of records. The majority of the records contain the actual data, but some record are header records. We do not actually need them. The real data records are fixed length (no delimiter). The header records are much shorter.
The problem is that DTS apparantly is not capable of distinguishing these records and mixes up data when a header record is encountered.
This is an example of the contents of the file:
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
header1
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
header2
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
header3
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
header4
field1field2field3xxfield4yyy
field1field2field3xxfield4yyy
.....
We have a workaround: we load the file in a table with one field, filter out the header records and treat the remaining data with sql statements using substring. But this is rather slow.
Has anybody an other solution for this problem that is faster?
Erik
February 27, 2004 at 6:34 am
Erik if you think that is slow you should revise the queries because so far I have found that to be the fastest! thing to do.
Do you use Bulkinsert to get the file in?
As far as I can tell there is not too much proccessing involved in that
* Noel
February 27, 2004 at 6:40 am
if you still want to use DTS Processing try to use the multiphase datapump feature,determine that you are using the header record and SKIP the row (returning DTSTransformStat_SkipRow)!
* Noel
February 27, 2004 at 6:41 am
It would be a lot faster if BCP or DTS could load the file directly in the target table, skipping the non-compliant rows.
These noon-compliant rows could be placed in an error file or table. Access does something similar.
Maybe this is a good feature request for Yukon.
Aparantly our 'workaround' is the best solution known at this time. But you never know.
Erik.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply