English

How To: Create a new table directly into a SQL Server instance with the Data Interoperability extension

Summary

Instructions provided describe how to create an attribute table of any supported file directly into an instance of SQL Server.

Procedure

This procedure cannot be performed through Quick Export; it is performed through a combination of Data Interoperability connections and a custom extract, transform, and load (ETL) tool.

Before completing the steps below, ensure the following:
· Write access is granted to SQL Server.
· The machine that is being used is connected to SQL Server.
· There is at least one table in the server.
· The default database is selected.

  1. Close all ArcGIS applications except ArcCatalog. Click Interoperability Connections > Add Interoperability Connection.
  2. Set the format to ODBC 3.x Database and then set the dataset to the SQL Server.
  3. Click Settings, set the database name to the SQL Server name, and enter in the UserID and password for SQL.
  4. Click the ellipsis next to Table List and select a current table on the SQL Server.

    Note:
    It does not matter which table is selected, but one table must be selected.

    Click OK in the Settings dialog box. Click OK in the Connection dialog box.
  5. In ArcToolbox window, right-click in an empty area and select New Toolbox. Name it. Then right-click on the new toolbox and select New Spatial ETL tool.
  6. Set the type to ESRI Shape. Click Next.

    Note:
    This example uses shapefiles; however, this works with any supported data type.

  7. Click the ellipsis and navigate to a shapefile with attributes. Click OK. Click Next.
  8. For the destination format, select ODBC 3.x Database. Click Next.
  9. Click Settings and then enter the SQL server name, user name and password. Click OK. Click Next.

    Warning:
    Do not click on the 'Import feature type definitions' checkbox.

    Click Next again. Click Finish. The Data Interoperability extension opens automatically and displays the ETL tool.
  10. Right-click the destination and select Properties. Make sure that the feature type name is unique. Click Ok.
  11. Click Run. Enter the SQL Server name in the ODBC connection prompt. Click OK.