Hugo Estrada's House of Wonder

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.