How To Import FileSystem Data Using DTS and Index Server (201785)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft OLE DB Provider for Index Server 2.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q201785

SUMMARY

This article describes how to import FileSystem data into SQL Server 7.0 using SQL Data Transformation Services (DTS) and Index Server OLE DB provider.

MORE INFORMATION

DTS Import Wizard does not show Index Server OLE DB Provider in the list of sources. However, you can specify a distributed query with Index Server OLE DB provider as the source of your data.

Steps to Copy FileSystem Data

  1. Run SQL Enterprise Manager and run Data Transformation Import Wizard.
  2. Choose SQL Server 7.0 as the source.
  3. Choose SQL Server 7.0 as destination. Specify the destination database.
  4. Choose the option "Use a query to specify the data to transfer," which opens a query window. Type a valid Index Server distributed query using OpenQuery or OpenRowset. For example: 
    SELECT a.*
FROM OpenRowset('MSIDXS','Web';''; '',  'Select Directory, FileName, size, Create, Write
 from SCOPE()
 where CONTAINS(''"Index Server" ~ "SQL Server"'')> 0
 and FileName LIKE ''%.htm%'' ') AS a
  5. Parse the query to make sure the SQL Statement is valid. Click Next to see information on source and destination table.

    You can click Transform if you need to apply custom transformations. You can also view the source data by clicking Preview.
  6. Click Next to run the DTS package.

REFERENCES

For more details on setting up and using Distributed Queries, please see:

SQL 7.0 Books Online: sp_addlinkedserver; OpenQuery; OpenRowset;

For more information on using MSDIXS provider, refer to the Index Server documentation in the Windows NT 4.0 Option Pack documentation.
Modification Type:MinorLast Reviewed:7/1/2004
Keywords:kbDatabase kbhowto KB201785
©2004 Microsoft Corporation. All rights reserved.