|
 |
 |
|
|
|
||
|
|
|
|
||||||||||||||||||||||||
 |
 |
 |
Note: Additional information is contained within the <database name>_instructions.txt file created during the file generation process. The <database name>_instructions.txt file contains the actual file names created by FmPro Migrator. This document uses an example FileMaker Pro database named "example", the file created for your database will use the name of the FileMaker database you are converting.
Note: FmPro Migrator for Windows requires that Perl be
installed on your Windows computer before generating scripts. Activestate
Perl may be downloaded from www.activestate.com. FmPro Migrator for
MacOS X utilizes the version of Perl which was automatically installed
with your operating system.
1) Fill in the fields within the FileMaker and Other tabs of the FmPro
Migrator application. For Windows, enter the FileMaker ODBC DSN on the
FileMaker tab. Make sure that the Local and Remote Data Access Companion
plug-ins are enabled for each FileMaker database. Each FileMaker database
should be configured with no password required for access to the entire
database, while FmPro Migrator is gathering info about the database.
2) Select or create the destination directory for the conversion files
which will be generated.
3) Save the configuration information by selecting Save As from the
File menu.
4) Open one (or multiple) FileMaker database files.
Note: If any FileMaker database files contain spaces, these should be
removed before opening the file.
5) Press the Migrate button to generate the conversion scripts and database
documentation files.
6) Create a new SQL Server database and a new SQL Server database user
account (if needed).
7) Transfer the example_fmpro_to_sqlserver_xfer_odbc1.pl program to
a Windows server. At the present time the example_fmpro_to_sqlserver_xfer_odbc1.pl
Perl script needs to be run from a Windows server due to the lack of
an ODBC driver for Mac OS X.
8) Install the Perl DBI and DBD::ODBC modules on the Windows
server along with the FileMaker ODBC Driver and the SQL Server ODBC
driver. The SQL Server driver will automatically be installed as part
of the SQL Server installation process. Therefore if this script will
be executed from the SQL Server machine this driver will already be
installed. Install the SQL Server client software if this script needs
to be executed from another Windows machine. (The DBI/DBD::ODBC modules
can be downloaded from www.cpan.org) or installed with Activestate PPM.
Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit
9) Create the example_fmp_dsn entry in the ODBC Control Panel. Change
the configuration of the Max Text length parameter from 255 to 65000
in the Advanced tab of the FileMaker DSN.
10) Enable the Local and Remote Data Access Companions within the FileMaker
application preferences dialog. Enable Multi-User access and both the
Local and Remote Data Access Companions by selecting Sharing from the
File menu of each database file. Each database should allow complete
access without requiring a password in order for FmPro Migrator to read
the structure of each database file.
11) Execute the example_fmpro_max_fieldsize1.pl program to produce a
report showing the maximum amount of data stored within each FileMaker
database field.
12) Examine the example_create_table1.sql file to determine if any changes
need to be made based upon the results listed in the example_fmpro_max_fieldsize_report.txt
report file. Transfer this file to the SQL Server server and execute
this code with the command line osql or isql utilities to create the
table which will contain the FileMaker data. [If a migration is done
from FileMaker 7.0v1 to SQL Server with FmPro Migrator for Windows,
please see the troubleshooting notes for more info about column type
issues.]
13) Change the name of the FileMaker Pro database to remove any spaces
or special characters, otherwise the SQL query which retrieves data
from FileMaker within the example_fmpro_to_sqlserver_xfer_odbc1.pl program
will fail. Then re-generate the conversion scripts.
Follow steps 14A - 15A if there is no primary key defined within the FileMaker database, otherwise follow steps 14B - 18B.
14A) Create the fmp_to_sqlsvr_dsn entry in the ODBC Control
Panel.
15A) Transfer the example_fmpro_to_sqlserver_xfer_odbc1.pl file to a
Windows server.
16A) Execute the example_fmpro_to_sqlserver_xfer_odbc1.pl program to
transfer the data from FileMaker to SQL Server via an ODBC network connection.
17A-18A) Skip to Step 19 if repeating fields need to be transferred
from FileMaker to SQL Server.
14B) Transfer the example_fmpro_to_sqlserver_xfer_odbc1.pl,
example_copy_temp_table1.sql, and example_temp_create_table1.sql files
to a Windows server. At the present time the example_fmpro_to_sqlserver_xfer_odbc1.pl
Perl script needs to be run from a Windows server due to the lack of
a FileMaker ODBC driver for Mac OS X.
15B) Create the fmp_to_sqlsvr_dsn entry in the ODBC Control Panel.
16B) Use the command line osql or isql utilities to create the temporary
example_temp_create_table SQL Server table using the example_temp_create_table1.sql
file.
17B) Execute the example_fmpro_to_sqlserver_xfer_odbc1.pl program to
transfer the data from FileMaker to the temporary example_temp_create_table
SQL Server table via an ODBC network connection.
18B) Use the command line osql or isql utilities to copy the data from
the temporary example_temp_create_table table to the permanent example
table using the example_copy_temp_table1.sql file.
Use the following instructions if there are repeating
fields which need transferred from FileMaker to SQL Server.
19) If repeating fields need to be extracted from the FileMaker database,
use the example_repeating_fields_create_table1.sql SQL script to create
the SQL Server example_repeating table for the repeating fields data.
The column sizes defined within this script should be adjusted as necessary
prior to creating this table.
20) Change each FileMaker repeating field to be a TEXT field so that
each of the repeating values will be transferred properly. (Note: Do
not make this change prior to generating the migration scripts or the
repeating fields will be created with the wrong datatypes.)
21) Execute the example_repeating_fields_xfer_odbc1.pl program to transfer
the repeating fields data from FileMaker to SQL Server via an ODBC network
connection.
22) Execute the example_drop_repeating_fields_columns1.sql SQL script
to drop the unneeded repeating fields columns from the SQL Server example
table.
----------- Usage Instructions - Image Export Script ------------
[Enterprise Edition feature]
The example_fmpro_image_export1.pl Perl script provides a JPEG image
export feature for FileMaker Pro databases. This script exports one
specified container field as a JPEG file into a user-defined directory
on the computer where this script is running. The name used for writing
the JPEG file is retrieved from a user-specified FileMaker database
field.
1) Fill in the fields of the FileMaker and either the Oracle or Other
tabs of the FmPro Migrator application.
2) Select or create the destination directory for the conversion files
which will be generated.
3) Save the configuration information by selecting Save As from the
File menu.
4) Open the FileMaker database file.
5) Press the Migrate button to generate the migration scripts and database
documentation files.
6) Create the example_fmpro_odbc_dsn ODBC DSN entry in the ODBC Control
Panel. Change the configuration of the Max Text length parameter from
255 to 65000 in the Advanced tab of the FileMaker DSN.
7) Make the following changes within the example_fmpro_image_export1.pl
Perl script in order to specify the name of the container field, filename
field and destination directory.
The following example shows that the container field has been named
"image_field", the filename field has been named "filename_field"
and the output directory is named "my_images" at the top level
of the C: drive. The output directory needs to be specified using \
directory separators on Windows and needs to contain a trailing directory
separator at the end of the directory name.
--------- Example code follows
my $fmpro_image_data_field = 'image_field';
my $fmpro_image_filename_field = 'filename_field';
my $image_output_directory_path = 'c:\\my_images\\';
---------
8) Execute the example_fmpro_image_export1.pl Perl script on a PC running
Windows (or Vitual PC running on a Macintosh) as follows:
perl example_fmpro_image_export1.pl
The JPEG images will be exported from the FileMaker database and written to the output directory. Repeat steps 7 and 8 as needed if there are multiple container fields which need exported.
# ----------------------- Usage Notes -----------------------
Large Text Fields
FileMaker fields containing more than 255 characters of text will be
converted to SQL Server TEXT columns when converted into the SQL Server
database. The list of these column names should be entered into the
Large Text Fields field. Data is transferred between FileMaker and SQL
Server by the example_fmpro_to_sqlserver_xfer_odbc1.pl program. This
program makes use of bind variables to specify field names and ODBC
field types.
Extra FileMaker Fields
If there are fields within the FileMaker database which don't need to
be transferred to the SQL Server database, these fields should be removed
before generating the conversion scripts. This can be easily accomplished
by making a copy of the FileMaker database, then removing the extra
fields from the copied database file. This technique reduces the chance
of making a mistake by manually editing the files generated by FmPro
Migrator.
ODBC driver and client software installation
Data is transferred from FileMaker to SQL Server by using the example_fmpro_to_sqlserver_xfer_odbc1.pl
program. This program is generated by FmPro Migrator based upon the
specified FileMaker database structure in order to facilitate the transfer
of data to SQL Server.
The example_fmpro_to_sqlserver_xfer_odbc1.pl program transfers data
for all FileMaker field types including text fields up to 64000 bytes
and JPEG image data from Container fields. The program makes use of
bind variables to specify field names and ODBC field types. The example_fmpro_to_sqlserver_xfer_odbc1.pl
program needs to be installed on a Windows server which has SQL Server
ODBC driver software, Perl, Perl DBI module, Perl DBD::ODBC module,
and FileMaker Pro ODBC driver software installed. This is due to the
lack of a FileMaker Pro ODBC driver for Mac OS X. The example_fmpro_to_sqlserver_xfer_odbc1.pl
script running on the PC can then read the data from FileMaker Pro (hosted
on either Mac OS, Mac OS X or Windows) and then be written into a SQL
Server database.
Repeating Fields
If repeating fields are detected within the FileMaker database, two
extra scripts will be created in order to extract the repeating fields
data from FileMaker to SQL Server. The example_repeating_fields_create_table1.sql
creates a new database table for the related data contained within the
repeating fields. The example_repeating_fields_xfer_odbc1.pl should
then be run in order to extract the repeating fields data into the new
example_repeating database table.
Notes:
It is necessary for FmPro Migrator to accurately determine the primary
key field within the FileMaker database in order to properly generate
the repeating fields extraction scripts. The primary key for the primary
database table is used to reference each repeating field record. Therefore
at least one field in the main database table must be set within FileMaker
with the UNIQUE and NOT EMPTY validation parameters. It is recommended
that this field be a numeric field using an auto-enter serial number
within FileMaker. This field will then be used as the primary key column
for the transferred data.
In order for all repeating field data values to be transferred from
FileMaker, the data type within FileMaker must be a Text field. Each
repeating field entry is separated by an ASCII (29) character which
is used by FileMaker Pro to separate values within repeating fields.
The example_repeating_fields_xfer_odbc1.pl script splits the data values
into separate records by using the ASCII (29) character as a record
delimiter and writes out each repeating field value into a new record
within the example_repeating table.
If repeating values are present within FileMaker numeric, date or time
fields, only the first value will be transferred to the destination
database. Therefore each repeating field needs to be converted to Text
fields within the Define Fields dialog box within FileMaker. This needs
to be done after creating the migration scripts. This change should
not be made within FileMaker before the migration scripts are created,
or the repeating field database table will be created with the wrong
datatypes (i.e. all fields will be TEXT).
FileMaker container fields which are configured as repeating fields
cannot be extracted.
Relationships
FileMaker Pro relationships are not automatically traversed and converted
by FmPro Migrator because full pathname info is not available for each
file. The FileMaker Pro developer should manually open each related
file and then FmPro Migrator STD Edition will create conversion scripts
for each open file. Please note that it may not be necessary to manually
open each related file because some files may be opened automatically
in the background by FileMaker. In this case, no additional action is
required. FmPro Migrator Lite edition requires the opening of each file
individually prior to starting the conversion process because only one
file at a time is processed.
Container Fields
FileMaker Pro uses Container fields for storing pictures, sound and
QuickTime video. Data located within FileMaker Pro Container fields
is transferred to SQL Server Image columns. There are some limitations
which have been discovered while retrieving Container field data from
FileMaker Pro. The only Container field data supported by transfer with
FmPro Migrator is JPEG image data. All container fields include the
text "HTTP/1.0" prefixed to the data. This header information
is removed from the data, with the remaining JPEG file data in the field
being transferred into the SQL Server database table. Other types of
container field data are also prefixed with the "HTTP/1.0"
file header information which is followed by a JPEG icon representing
the type of data contained within the field.
FileMaker Pro does not support writing Container field data into a FileMaker Pro database via an ODBC connection on either Windows or Macintosh platforms.
Container Field Workaround:
One workaround to the issue of transferring container field data is
to store only a pathname to the data within the FileMaker Pro database.
The example_fmpro_to_sqlserver_xfer_odbc1.pl program can be modified
to read the pathname from the FileMaker Pro database, then read the
binary data directly from a local or remotely shared directory. An example
of this type of modification is included in the example_fmpro_to_sqlserver_xfer_odbc21.pl
program.
Working with FileMaker Pro on Windows - FmPro Migrator EE Edition can work with FileMaker Pro databases running on Windows servers for the data transfer phase of the conversion process. However a copy of the FileMaker Pro database must be running under Mac OS X in order for FmPro Migrator to gather the list of fields and field attributes information. FmPro Migrator cannot retrieve database structure information from stand-alone applications created for the Windows platform because Apple Events are not available on Windows. Furthermore, it is not possible to retrieve data from stand-alone applications via ODBC due to the lack of networking with stand-alone solution files.
Working with Stand-Alone Solutions Files - FmPro Migrator EE Edition can read the database structure from stand-alone solutions files created with FileMaker Pro Developer on Mac OS X. If the developer of the solution has enabled file exporting, then a tab or comma separated value export file may be created. This export file may then be imported into SQL Server with Data Transformation Services feature. However it will not be possible to extract the information from the file by using an ODBC connection because networking capability is not available with FileMaker stand-alone files. The stand-alone file will also need to be the Macintosh version of the executable in order for Apple Events used by FmPro Migrator to read the structure of the database file.
Primary Key Determination - FmPro Migrator examines the structure of the FileMaker Pro database in order to determine which column should represent the primary key for the SQL Server database table. The primary key column of the SQL Server database table will be migrated as a primary key column using a compatible datatype derived from the original FileMaker database. FmPro Migrator selects the first FileMaker Pro field which is configured with UNIQUE and NOT NULL options as the primary key. This determination is made by examining each field option in the field creation order, as is listed on the database structure report. To insure that FmPro Migrator selects the correct field as the primary key, it may be necessary to temporarily disable either the UNIQUE or NOT NULL options for other database fields while generating conversion scripts.
SQL Server IDENTITY fields - SQL Server databases support a column parameter listed as IDENTITY in the table design window. Due to limitations with the use of the IDENTITY_INSERT = ON feature with ODBC connections, a temporary table is created in the SQL Server database whenever a primary key is utilized in the FileMaker database. The data is imported into this temporary table with the example_fmpro_to_sqlserver_xfer_odbc1.pl Perl program. The data is then copied from the temporary table into the permanent table named example with the example_copy_temp_table1.sql file. The IDENTITY starting and increment values may then be reset with the DBCC CHECKIDENT('example',RESEED,1000) command, where the number 1000 is replaced with the next identity value.
Exporting Data - All table data formats may be exported via the bcp program supplied with the database by Microsoft. The following command line provides a bcp example using the example database table:
bcp test.user1.example out example.dmp -n -U user1 -P user1pwd
Importing Data - All table data formats may be imported via the bcp program as shown with the following example:
bcp test.user1.example in example -E -n -U user1 -P user1pwd
Report Differences
FmPro Migrator for Windows generates a slightly different database report
compared to FmPro Migrator for MacOS X. FmPro Migrator for Windows utilizes
a ODBC connection to the FileMaker database since AppleScript is not
available for Windows. The only information available about the database
thru an ODBC connection is:
The Name of the Database
The List of Field Names
The Type of each Field
The Empty Ok attribute for each Field
The following information listed on the report consists
of default values used to fill in the report:
Field IDs (always an incrementing value)
Unique Values (always set to Not Unique)
Repeating Values (always set to No - unless entered manually into FmPro
Migrator)
Global Values (always set to No)
Access Status for each field (always set to read/write)
Protection Status for each field (always set to formulas/protected)
Calculation for each field (always blank)
Script Names (none listed)
Layout Names (only 1 listed - named All Fields, containing all fields)
|
||
|
|
|
|
||
