Data Migration & Integration Configuration

Configure MIcrosoft SQL Server DTS Import using the Microsoft OLE DB Provider

Use the following instructions to configure a DTS Import.

  1. Open your Microsoft SQL Server Enterprise Manager.
  2. Drill down to Console Root >> Microsoft SQL Servers >> SQL Server Group >> Your Actual SQL Server
  3. Expand the Security folder.
  4. Right click on Linked Servers.
  5. Select "New Linked Server."
  6. Supply the following information on your General tab:
    • Linked Server: Any descriptive name (no spaces)
    • (Check the "Other Data Source" radio button)
    • Provider Name: Microsoft OLE DB Provider for ODBC Drivers
    • Product Name: MSDASQL
    • Data Source: Your ODBC Data Source Name
  7. Do not check any server options on the bottom of the tab.
  8. Provide your Data Source username and password on the Security tab.
  9. Click OK to create your server.
  10. Exit the Linked Server dialog box.
  11. Locate your newly created Linked Server.
  12. Expand the new Linked Server's tree menu.
  13. Click the Tables icon.
  14. Locate the qabasics table in the tables list.
  15. Carefully record the Schema and Catalog associated with the table. For example:


    Name Schema Catalog Type qabasics dbo master User

  16. Expand the Microsoft SQL Server Enterprise Manager's Tools menu.
  17. Select the Microsoft SQL Server Query Analyzer.
  18. Build your query using the following guidelines:


    select * from [your linked server name].[catalog].[schema].qabasics

    For example:


    select * from SQL2KLA.master.dbo.qabasics

    Note: Leave blanks in place holders when database lacks catalog or schema information. For example:


    select * from ORA9iLA..SCOTT.qabasics

  19. Click the green arrow icon to run your query.