Hugo Estrada's House of Wonder

So here is the task: I have to extract a series of ESRI's geodtabases into a SQL Server 2005 database. Each geodatabese should have its own schema in sql server. And I can't use FME, but I do have access to ArcGIS. I asked my coworkers how I could do this, and they quickly gave me instructions on how to export the geodatabse into a sql geodatabase. Once there, I had to do queries on sql to move those tables into its destination database with the expected schemas.

This is what I did. It is a long story since there were many hiccups along the way, and I want to document those.

1. Getting the right arcGIS components

* I already have ArcGIS Desktop, but I needed to install ArcSDE to be able to have access to the utilities that import and export geodatabases.

* After installing ArcSDE, I needed to run post-installation scripts. I had to do this several times since every time I tried it gave me an error, and I had to export the tables into a correctly configured sql sde database. When it asked me for the login method, I kept saying to use Windows Authentication. When it later asked about the DBA user for the SDE server, I invented a new user. Obviously this didn't work, so I ended up creating a login and user in Sql Server. After doing this, then the post installation was able to finish successfully.

Update: I had to do this again, and I ran into another problem: it wouldn't let me finish the post-installation. The problem was that in the second and final screen, one is supposed to use the sa credentials to actually make the sde database and tables and to be able to create indices for xml.

* When I first attempted to run the the export scripts, I got an error that said it couldn't load the 'os module'. I quickly discovered that this was a python error. ArcGIS 9.3 requires to have python 2.5 and NumPy 1.0.1. First, I didn't know that they were required because I had both components when I installed ArcGIS Desktop into my computer. Second, they were gone because I spring cleaned my computer, and decided that I didn't want to have so many copies of python. So, installing these two missing components fixed the problem, and got the commands to work.

*

2. Exporting geodatabase into sql geodatabase

Once you have the needed components, you

* Open ArcCatalog and find the geodatabase that you want to export. you right click on it, and go to Export->Export to Geodatabase(Multiple)...

* There you select the sde database connection that you want, and you run the script.

After doing this, you will end up with the tables in your sde sql server database.

3. Moving the tables from from the sde database into the destination database

* In my case, I needed to move each geodatabase into its own schema. For each database, run the following query in the destination database: CREATE SCHEMA AUTHORIZATION dbo GO

Example: CREATE SCHEMA foo AUTHORIZATION dbo GO

* Then just send the table from one database into the other with this statement
select * INTO ..

Example: SELECT * INTO company.foo.restaurants FROM sdedb.dbo.restaurants

And you are done.