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.

The problem:
Create a sql server daily export where:
a) You must start with an empty access file
b) You must move the newly created export file to another directory while keeping a copy in the working directory

The solution:
1. Create a SSIS package to run the export job from the command line
2. Create a ruby script to do all of the file dancing required for a and b
3. Create a scheduled job

This task seemed to me like an obvious command line job since it required the constant copying of an empty access file to become the output file. This step seemed necessary to me because whenever I attempted to export to an access file that already had some data, I would get an error message. Also, I couldn't figure out how to get sql studio 2005 to just create the access file; it seems as if it required it to exist already. Maybe it is different if one has Access installed in the computer, but we don't to avoid the security risk that it entails.

The server already has ruby installed, so I decided to write the script using ruby. Had I been working on a unix server, I would have just used a batch file. I could have installed cygwin, but I don't want to install more software than it is needed to the server. I could have used a windows batch script, but since I already have ruby installed, it was easier to use it.

Furthermore, ruby scripts tend to be so small and easy to read, that I see it as an advantage to write scripts with Ruby.

Steps:
1. Create the SSIS
The instructions are here SQL Server 2005 Import / Export Wizard, Database Journal. The key element in the export is to save SSIS package as a file.

2. Write the ruby code

# run_db_export.rb
# This script will run an export a db.
# It will move the output to ftp folder for easy access.
# It should be put on a scheduled job to
# to keep running each day.
 
require 'ftools'
 
# Let's copy the blank access file and call it output
File.copy('blank.mdb', 'output.mdb')
 
# Let's run the export
success = system 'dtexec /f export_pck.dtsx'
 
# Now let's put a copy in the output  area
if success then 
  File.copy('output.mdb', 'exported\\hifld_db.mdb')
else
  File.open('error.txt', 'a') { |f|
    f.puts "Failed db export attempt on #{Time.now}"
  }
end 

3. Now just set up your schedule job. Here are instructions on how to do it. http://support.microsoft.com/kb/308569 Make sure that you select the ruby script that you created. Also, keep in mind that if you created the SSIS with the default security in it, you must run that script with the credentials that you used to create it.

These guys will first allow you to insert an identity into a sql server table, and then turn off that power.

SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF

bogus example:

SET IDENTITY_INSERT funkyFooTable ON

insert into funkyFooTable
(id, funkyBarColumn) values (31415, 'Tasty pie....')

SET IDENTITY_INSERT funkyFooTable OFF

And here is the source of this tidbit with a better explanation on how to use it. Thanks, Ben Nadel!
http://www.bennadel.com/blog/24-Turning-Off-and-On-Identity-Column-in-SQ...