Question:
I’m looking for other tools for importing ASCII files to SQL server 7.0 without problems. At this moment I’m missing data and the tools can’t take care of it and I don’t have any controls to find out if something is going wrong with my data. I want to buy a tool if you know something about it.
Answer:
BCP (bulk copy program) is probably the fastest thing known to man, but if you’re in the mood to investigate, you should check out OLE DB through the Data Transformation Services (DTS) interface. OLE DB makes non-tabular data understandable to databases. Using OLE DB, DTS performs import or export with the same power to detect and correct errors using SQL. If you can’t frame your insert or update statement in one pass, you can apply the data transformation rules a line at a time in a VBScript DTSSource.t.c = DTSDestination.t.c
, but that can take a lot of time.
There’s also the Bulk Insert tasks that files, but I don’t think you get the value added from DTS Select language, VBScripts or lookup tables.