How To Import FileSystem Data Using DTS and Index Server

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.

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.

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.