Starfish ETL - User contributions [en]

Help

2015-04-09T15:38:48Z

Admin:

MediaWiki documentation covers the whole of an extremely complex and powerful Web application, namely the one that runs Wikipedia and most other widely-used encyclopedic websites. While this wiki doesn't restrict the use of any core component or feature of MediaWiki, we care more about a few features than most of the others available to you while creating and editing pages. Here we will attempt to document those.

* First, wiki link syntax. To add an internal link, simply put two sets of brackets around the desired title of the page (it may or may not exist, but presumably you'll know whether it does). Like so:
<nowiki>
[[A Wiki Page]] can be reached by clicking a link.
</nowiki>
To rename the link or to link to a page whose title doesn't match the text, separate the page title from the displayed link text with a pipe ("|" character):
<nowiki>
[[A Wiki Page|The text that displays]] need not match the title of the page to which it links.
</nowiki>

==Links for Getting Started==
* [//www.mediawiki.org/wiki/Manual:Configuration_settings Configuration settings list]
* [//www.mediawiki.org/wiki/Manual:FAQ MediaWiki FAQ]
* [https://lists.wikimedia.org/mailman/listinfo/mediawiki-announce MediaWiki release mailing list]
* [//www.mediawiki.org/wiki/Localisation#Translation_resources Localise MediaWiki for your language]

Help

2015-04-09T15:38:24Z

Admin:

MediaWiki documentation covers the whole of an extremely complex and powerful Web application, namely the one that runs Wikipedia and most other widely-used encyclopedic websites. While this wiki doesn't restrict the use of any core component or feature of MediaWiki, we care more about a few features than most of the others available to you while creating and editing pages. Here we will attempt to document those.

* First, wiki link syntax. To add an internal link, simply put two sets of brackets around the desired title of the page (it may or may not exist, but presumably you'll know whether it does). Like so:
<nowiki>
[[A Wiki Page]] can be reached by clicking a link.
</nowiki>
To rename the link or to link to a page whose title doesn't match the text, separate the page title from the displayed link text with a pipe ("|" character):
<nowiki>
[[A Wiki Page|The text that displays]] need not match the title of the page to which it links.
</nowiki>

==Links for getting Started==
* [//www.mediawiki.org/wiki/Manual:Configuration_settings Configuration settings list]
* [//www.mediawiki.org/wiki/Manual:FAQ MediaWiki FAQ]
* [https://lists.wikimedia.org/mailman/listinfo/mediawiki-announce MediaWiki release mailing list]
* [//www.mediawiki.org/wiki/Localisation#Translation_resources Localise MediaWiki for your language]

Help

2015-04-09T15:37:03Z

Admin: Created page with "MediaWiki documentation covers the whole of an extremely complex and powerful Web application, namely the one that runs Wikipedia and most other widely-used encyclopedic websi..."

Locate your Web Service URL

2014-11-11T20:10:10Z

Admin:

The Starfish ETL Web Service URL is typically [http://localhost/StarfishEngine/StarfishService.asmx| http://localhost/StarfishEngine/StarfishService.asmx]

If that doesn't work you can find it manually in the IIS Manager (Control Panel > Administrative Tools > Internet Information Services Manager). Browse your available sites on the left sidebar. Look for StarfishEngine and click on it. Then along the right side click Browse and that will take you to the URL.

[[File:Web_Service_URL.png|1000px]]

License Key

2014-11-07T16:15:23Z

Admin:

To run a job using Starfish, you must supply your license key. To use the software in trial mode requires a trial license. If you do not have a license key, please contact [mailto:info@starfishetl.com?subject=License Key Starfish ETL] to obtain one. To apply your key, from the General tab, click the "Update License" button.

[[file:Updatelicense.png]]

Then paste your key in the box, click OK.

[[file:Licensewindow.png]]

License Key

2014-11-07T16:14:15Z

Admin:

To run a job using Starfish, you must supply your license key. To use the software in trial mode requires a trial license. If you do not have a license key, please contact [mailto:info@starfishetl.com?subject=License+Key Starfish ETL] to obtain one. To apply your key, from the General tab, click the "Update License" button.

[[file:Updatelicense.png]]

Then paste your key in the box, click OK.

[[file:Licensewindow.png]]

Installation

2014-11-07T16:10:33Z

Admin: /* Installation Instructions */

=Installation Instructions=
*[[Server Requirements]]
*[[Starfish Engine]]
**[[Locate your Web Service URL]]
**[[Engine Web.Config Settings]]
*[[Starfish Admin]]
**[[License Key|Configure License Key]]
*[[Starfish Scheduler]]
**[[Scheduler Config Settings]]

*[[Starfish Updater]]
*[[System Backup Wizard]]

Installation

2014-11-07T16:10:19Z

Admin: /* Installation Instructions */

=Installation Instructions=
*[[Server Requirements]]
*[[Starfish Engine]]
**[[Locate your Web Service URL]]
**[[Engine Web.Config Settings]]
*[[Starfish Admin]]
**[[Configure License Key|License Key]]
*[[Starfish Scheduler]]
**[[Scheduler Config Settings]]

*[[Starfish Updater]]
*[[System Backup Wizard]]

Main Page

2014-11-05T23:02:14Z

Admin:

The Starfish ETL Wiki was created to provide collaborative documentation of [http://www.starfishETL.com StarfishETL] and related topics. Feel free to contribute in any way that you can. If something's missing, add it. If something's messed up, fix it. Anyone can edit our pages by requesting an account.

For editing help and a list of things you can help us with, see '''[[Help]]'''. If you have any questions about editing or access to the Starfish ETL Wiki, please send us a message at [mailto:info@starfishetl.com info@starfishetl.com].

Find detailed information about each Connector type in the [[Connectors]] section.

----
'''General topics'''
{| width="100%"
|- valign="top"
| width="14.3%" align="center" | [[File:Monobook_icon.svg|60px|link=Installation]]
| width="14.3%" align="center" | [[File:Document-properties.png|60px|link=Settings]]
| width="14.3%" align="center" | [[File:Administration.png|60px|link=Job Operations]]
| width="14.3%" align="center" | [[File:Applications-internet.svg|60px|link=Connectors]]
| width="14.3%" align="center" | [[File:Nuvola mimetypes source.svg|60px|link=Scripting]]
| width="14.3%" align="center" | [[File:Nuvola apps filetypes.svg|60px|link=FAQs]]
| width="14.3%" align="center" | [[File:Green bug and broom.svg|60px|link=Troubleshooting]]
|}
{| width="100%"
|- valign="top"
| width="14.3%" align="center" | '''[[Installation]]'''
| width="14.3%" align="center" | '''[[Settings]]'''
| width="14.3%" align="center" | '''[[Job Operations]]'''
| width="14.3%" align="center" | '''[[Connectors]]'''
| width="14.3%" align="center" | '''[[Scripting]]'''
| width="14.3%" align="center" | '''[[FAQs]]'''
| width="14.3%" align="center" | '''[[Troubleshooting]]'''
|}

General Help

2014-11-05T22:54:57Z

Admin: /* Mapping */

*[[Create a New Job]]
*[[Copying a Job]]
*[[Scheduling Jobs]]
*[[Previewing Jobs]]
*[[Running Jobs]]
**[[Run Options]]
**[[Using Multi-threading]]
*[[Saving Jobs]]

=Connections=
*[[Configuring the Origin]]
*[[Configuring the Destination]]
*[[Using Transactions]]

=Mapping=
*[[Creating a Stage]]
*[[Stage Options]]
*[[Mapping Screen Overview]]
*[[Straight Field-to-Field Mapping]]
*[[Function Field Mapping]]
*[[Exec Before/After Operations]]
*[[Inserting Variables]]
*[[Starfish Scripting Class]]
*[[Cross-reference (Xref)]]

Exec Before/After Operations

2014-11-05T22:54:24Z

Admin:

Any number of functional operations can be performed before or after a Job is processed. To set up the operations, click the Add/Edit link for Exec Before (on the top of the screen) or Exec After (on the bottom of the screen)

[[file:Beforeoperations.png]]

Enter a meaningful name, select the [[Action Types|Action Type]], assign a variable name where necessary, set the [[Execute When|Exec When]] option, and configure the Action.

Function Field Mapping

2014-11-05T22:53:08Z

Admin:

To begin creating a Function Field, you can do this 3 ways.

1. Select “Function Field” from the Origin Fields drop-down.To begin creating a Function Field, select “Function Field” from the Origin Fields drop-down.

[[file:functionfieldmapping.png]]

2. OR click the ellipsis inside the Operation column (or double-click this field).

[[file:functionfield2.png]]

3. OR click the + button

[[file:functionfield3.png]]

Select from the following types of Function Fields, more information is provided below.

{| class="wikitable"
! width="175" | Option Name
! Description
|-
| SQL Lookup
| Performs a database lookup on the Destination database. Supply a SQL SELECT statement, will return the first column from the first row. More on [[SQL Lookups|Server-Side Lookups]]
|-
| Smart Lookup
| Performs a lookup on the destination, returning a single field’s value. Allows caching of results so the multiple runs of the same lookup don’t have to make round-trips to the destination. This cache is stored in memory, so it’s a good idea to only use it when there is a high chance of the same queries, otherwise an out of memory exception could occur when caching large amounts of data.
|-
| Hardcoded Value
| Allows the user to specify a static value, although you can also insert aVariable here as well. It is not necessary to include quotes for text/string values.
|-
| Scripted Value
| Allows the user to write a VBScript function to perform custom operations for each field. The VBScript function provides a skeleton function called “ScriptedField”. The value that will be used is whatever is returned out of this function. More on [[Scripted Value|Scripted Values]]
|-
| SalesLogix ID
| For OLE DB destinations using the SalesLogix OLE DB adapter, this function will be available. Automatically generated a new Saleslogix ID for this field, based on the Stage’s destination table.
|-
| Format Conversion
| Convert source data to another format. Options are:Rich Text (RTF) -to- Plain TextHTML -to- Plain TextBase64 -to- BinaryBinary -to- Base64File -to- Base64
|-
| Current Date/Time
| Inserts the current Date/Time at the server. Formats include Date/Time, Date Only, Time Only.
|}

Function Field Mapping

2014-11-05T22:51:00Z

Admin:

To begin creating a Function Field, you can do this 3 ways.

1. Select “Function Field” from the Origin Fields drop-down.To begin creating a Function Field, select “Function Field” from the Origin Fields drop-down.

[[file:functionfieldmapping.png]]

2. OR click the ellipsis inside the Operation column (or double-click this field).

[[file:functionfield2.png]]

3. OR click the + button

[[file:functionfield3.png]]

Select from the following types of Function Fields, more information is provided below.

{| class="wikitable"
! width="175" | Option Name
! Description
|-
| SQL Lookup
| Performs a database lookup on the Destination database. Supply a SQL SELECT statement, will return the first column from the first row. More on [[SQL Lookups|Server-Side Lookups]]
|-
| Smart Lookup
| Performs a lookup on the destination, returning a single field’s value. Allows caching of results so the multiple runs of the same lookup don’t have to make round-trips to the destination. This cache is stored in memory, so it’s a good idea to only use it when there is a high chance of the same queries, otherwise an out of memory exception could occur when caching large amounts of data.
|-
| Hardcoded Value
| Allows the user to specify a static value, although you can also insert aVariable here as well. It is not necessary to include quotes for text/string values.
|-
| Scripted Value
| Allows the user to write a VBScript function to perform custom operations for each field. The VBScript function provides a skeleton function called “ScriptedField”. The value that will be used is whatever is returned out of this function. More on [[Scripted Values]]
|-
| SalesLogix ID
| For OLE DB destinations using the SalesLogix OLE DB adapter, this function will be available. Automatically generated a new Saleslogix ID for this field, based on the Stage’s destination table.
|-
| Format Conversion
| Convert source data to another format. Options are:Rich Text (RTF) -to- Plain TextHTML -to- Plain TextBase64 -to- BinaryBinary -to- Base64File -to- Base64
|-
| Current Date/Time
| Inserts the current Date/Time at the server. Formats include Date/Time, Date Only, Time Only.
|}

Installation

2014-11-05T21:38:48Z

Admin: /* Installation Instructions */

=Installation Instructions=
*[[Server Requirements]]
*[[Starfish Engine]]
**[[Locate your Web Service URL]]
**[[Engine Web.Config Settings]]
*[[Starfish Admin]]
*[[Starfish Scheduler]]
**[[Scheduler Config Settings]]

*[[Starfish Updater]]
*[[System Backup Wizard]]

Starfish Updated

2014-11-05T21:38:22Z

Admin: Admin moved page Starfish Updated to Starfish Updater

#REDIRECT [[Starfish Updater]]

Starfish Updater

2014-11-05T21:38:21Z

Admin: Admin moved page Starfish Updated to Starfish Updater

Starfish ETL has a built-in updater. To start, click the Starfish button and select “Check for Updates” or launch StarfishUpdater.exe directly from within the Starfish Admin installation path.

[[file:Checkforupdates.png]]

The first time the Updater is run, you'll have to supply the paths to the Starfish ETL™ components. If your Starfish Engine is installed on another server, UNC paths are supported. If you don’t have a certain component installed (such as the Scheduler), you may leave the path empty.

[[file:Updater.png]]

Now you may click the “Look for Updates” button. If the updater finds out of date or missing files, the “Update Now” button will become available. (Files are compared by MD5 hash) Before proceeding, it is recommended that you close out Starfish Admin and shut down the Starfish Scheduler service if running. Now you may press the “Update Now” button. This will download and replace the necessary files from Starfish ETL™ servers.

[[file:Updateinprogress.png]]

Locate your Web Service URL

2014-11-05T21:36:48Z

Admin:

The Starfish ETl Web Service URL is typically [http://localhost/StarfishEngine/StarfishService.asmx| http://localhost/StarfishEngine/StarfishService.asmx]

If that doesn't work you can find it manually in the IIS Manager (Control Panel > Administrative Tools > Internet Information Services Manager). Browse your available sites on the left sidebar. Look for StarfishEngine and click on it. Then along the right side click Browse and that will take you to the URL.

[[File:Web_Service_URL.png|1000px]]

Locate your Web Service URL

2014-11-05T21:36:13Z

Admin:

System Backup Wizard

2014-11-05T21:08:40Z

Admin:

The Starfish ETL System Backup Wizard will walk you through a series of steps to create a complete backup framework for any system. The System Backup Wizard does not actually back up any data during this process, it only creates the tables and jobs to do so. When the wizard has completed, then you must run the jobs to backup the actual data.

You will first have to verify your Origin and Destination connections for compatibility. The connections for the currently selected Job will be used. The wizard works with any Origin connection type. The Destination must be of a type which support SQL statements (typically any current RDBMS).

When you complete the wizard, it will automatically:

*Create Tables inside of your backup database to match the schema or your Origin tables.
*Create Data maps for each table to back them up.

It may be necessary for you to manually alter the generated table definitions and maps to fit your needs.

Follow the steps below:

1. Create a new job. Set up the Origin & Destination. Your origin query/table selected does not matter in the wizard, but will be necessary to populate to get a valid connection.

2. Start the wizard by clicking the “System Backup Wizard” button on the General tab.

[[file:BackupWizardButton.png]]

3. '''Welcome to the System Backup Wizard'''

Read the introduction & click Next.

[[file:BackupWizard1.png]]

4. '''Connector Compatibility Validation'''

Next your connections will be validated. If your connections test ok and your destination is of a compatible type, you will be allowed to proceed.

[[file:BackupWizard2.png]]

5. '''Select Origin Tables to include in Backup Scripts'''

You will be presented with a check list box with all of the tables in your Origin. Select any table(s) that you will want to include in your backup.

[[file:BackupWizard3.png]]

6. '''Table Creation Settings'''

Fill in the information for creating the new destination backup tables. This is where the data will be backed up to.

[[file:BackupWizard4.png]]

{| class="wikitable"
! Option
! Description
|-
| Generate matching schema
| Uncheck this option to skip this step during the wizard (if you already have created the necessary tables)
|-
| Table Prefix
| Will prefix each table name with the value supplied (ie: prefix “bak_” for table “account” will become “bak_account”.
|-
| Default Type
| If a data type is not given by the connector or cannot be resolved through the data_type.map translation file, this value will be used.
|-
| Default Length
| If a type is specified, but not a length this value will be used.
|-
| Maximum Length
| If a value is supplied, and the length specified by the Origin is greater than this number, the length will be changed to this value.
|-
| Change if Maximum Length is exceeded
| If the length specified by the Origin is greater than the Maximum Length, the field data type is changed to this.
|-
| Drop Existing Tables
| Before a table is created by the Wizard a Drop Table command will be issued.
|}

7. '''Map Generation Settings'''

Fill in the settings for how you would like your jobs created.

[[file:BackupWizard5.png]]

{| class="wikitable"
! Option
! Description
|-
| Generate Jobs
| Uncheck this option to skip this step during the wizard.
|-
| Job Name
| This value will be used when creating each job’s name. The “%tablename” variable will be replaced with the actual table name when the wizard runs.
|-
| Stage Type
| One stage will be created for each job, with each field mapped directly across. This specifies that Stage Type: Insert, Update, Delete, Update/Insert.
|-
| Guess Job’s Primary Key for Match.
| Valid only for Update, Delete, Update/Insert Stage Types. Attempts to place the Match checkbox on the table’s Primary Key.

Here is the logic used for each connector:

Microsoft CRM: The Primary Key field is retrieved through the Metadata API for each table.

NetSuite: The "internalID" field will be used for each table.

Sage CRM: The Table ID will be using matching format (tableprefix + "_" + tablename + "ID") ie: comp_CompanyID

Salesforce: The "Id" field will be used for each table.

SData: The "Key" field will be used for each table.

SQL Server: First the table is evaluated for a Primary Key. If one does not exist then Unique Indexes are used. If there are no Unique Indexes, then regular indexed fields are used.

SugarCRM: The "id" field will be used for each table.
|-
| Chain all Jobs together
| All jobs will be chained together to run after another.
|}

8. '''Last Run Settings'''

This option will create the following:
*An additional table in your Destination database to track individual job last run dates
*A default filter on your Origin query to only select data from the last run date
*Logic to look up this last run date, and save/overwrite it when the job finishes

Then press Start to begin.

[[file:BackupWizard6.png]]

{| class="wikitable"
! Option
! Description
|-
| Only pull data changed from last run
| Check this option to process this step during the wizard.
|-
| Last Run Table
| Name of the table inside your Destination where the Last Run Dates for each job will be stored.
|-
| Default Filter
| For SQL-based connectors, will be appended as a WHERE clause to Origin query; otherwise will be populated as the Origin criteria box.
|-
| Default Format
| Date format to use in in filter/criteria.
|-
| Initial Date
| Date to initially populate in the Last Run table for each job. The first time the job is run, it will pull data starting from this date based on the filter clause.
|}

9. '''Backup Wizard is now Generating'''

Please wait while each table & job is created

[[file:BackupWizard7.png]]

10. '''System Backup Wizard Complete'''

You may click the link to view the log file generated, or open it directly from the Starfish Admin path, file named systembackup.log.

[[file:BackupWizard8.png]]

System Backup Wizard

2014-11-05T21:07:22Z

Admin:

The Starfish ETL System Backup Wizard will walk you through a series of steps to create a complete backup framework for any system. The System Backup Wizard does not actually back up any data during this process, it only creates the tables and jobs to do so. When the wizard has completed, then you must run the jobs to backup the actual data.

You will first have to verify your Origin and Destination connections for compatibility. The connections for the currently selected Job will be used. The wizard works with any Origin connection type. The Destination must be of a type which support SQL statements (typically any current RDBMS).

When you complete the wizard, it will automatically:

*Create Tables inside of your backup database to match the schema or your Origin tables.
*Create Data maps for each table to back them up.

It may be necessary for you to manually alter the generated table definitions and maps to fit your needs.

Follow the steps below:

1. Create a new job. Set up the Origin & Destination. Your origin query/table selected does not matter in the wizard, but will be necessary to populate to get a valid connection.

2. Start the wizard by clicking the “System Backup Wizard” button on the General tab.

[[file:BackupWizardButton.png]]

3. '''Welcome to the System Backup Wizard''' Read the introduction & click Next.

[[file:BackupWizard1.png]]

4. '''Connector Compatibility Validation''' Next your connections will be validated. If your connections test ok and your destination is of a compatible type, you will be allowed to proceed.

[[file:BackupWizard2.png]]

5. '''Select Origin Tables to include in Backup Scripts''' You will be presented with a check list box with all of the tables in your Origin. Select any table(s) that you will want to include in your backup.

[[file:BackupWizard3.png]]

6. '''Table Creation Settings'''

[[file:BackupWizard4.png]]

{| class="wikitable"
! Option
! Description
|-
| Generate matching schema
| Uncheck this option to skip this step during the wizard (if you already have created the necessary tables)
|-
| Table Prefix
| Will prefix each table name with the value supplied (ie: prefix “bak_” for table “account” will become “bak_account”.
|-
| Default Type
| If a data type is not given by the connector or cannot be resolved through the data_type.map translation file, this value will be used.
|-
| Default Length
| If a type is specified, but not a length this value will be used.
|-
| Maximum Length
| If a value is supplied, and the length specified by the Origin is greater than this number, the length will be changed to this value.
|-
| Change if Maximum Length is exceeded
| If the length specified by the Origin is greater than the Maximum Length, the field data type is changed to this.
|-
| Drop Existing Tables
| Before a table is created by the Wizard a Drop Table command will be issued.
|}

7. '''Map Generation Settings''' Fill in the settings for how you would like your jobs created.

[[file:BackupWizard5.png]]

{| class="wikitable"
! Option
! Description
|-
| Generate Jobs
| Uncheck this option to skip this step during the wizard.
|-
| Job Name
| This value will be used when creating each job’s name. The “%tablename” variable will be replaced with the actual table name when the wizard runs.
|-
| Stage Type
| One stage will be created for each job, with each field mapped directly across. This specifies that Stage Type: Insert, Update, Delete, Update/Insert.
|-
| Guess Job’s Primary Key for Match.
| Valid only for Update, Delete, Update/Insert Stage Types. Attempts to place the Match checkbox on the table’s Primary Key.

Here is the logic used for each connector:

Microsoft CRM: The Primary Key field is retrieved through the Metadata API for each table.

NetSuite: The "internalID" field will be used for each table.

Sage CRM: The Table ID will be using matching format (tableprefix + "_" + tablename + "ID") ie: comp_CompanyID

Salesforce: The "Id" field will be used for each table.

SData: The "Key" field will be used for each table.

SQL Server: First the table is evaluated for a Primary Key. If one does not exist then Unique Indexes are used. If there are no Unique Indexes, then regular indexed fields are used.

SugarCRM: The "id" field will be used for each table.
|-
| Chain all Jobs together
| All jobs will be chained together to run after another.
|}

8. '''Last Run Settings'''

This option will create the following:
*An additional table in your Destination database to track individual job last run dates
*A default filter on your Origin query to only select data from the last run date
*Logic to look up this last run date, and save/overwrite it when the job finishes

Then press Start to begin.

[[file:BackupWizard6.png]]

{| class="wikitable"
! Option
! Description
|-
| Only pull data changed from last run
| Check this option to process this step during the wizard.
|-
| Last Run Table
| Name of the table inside your Destination where the Last Run Dates for each job will be stored.
|-
| Default Filter
| For SQL-based connectors, will be appended as a WHERE clause to Origin query; otherwise will be populated as the Origin criteria box.
|-
| Default Format
| Date format to use in in filter/criteria.
|-
| Initial Date
| Date to initially populate in the Last Run table for each job. The first time the job is run, it will pull data starting from this date based on the filter clause.
|}

9. '''Backup Wizard is now Generating''' Please wait while each table & job is created

[[file:BackupWizard7.png]]

10. '''System Backup Wizard Complete''' You may click the link to view the log file generated, or open it directly from the Starfish Admin path, file named systembackup.log.

[[file:BackupWizard8.png]]

File:BackupWizard5.png

2014-11-05T21:07:17Z

Admin:

System Backup Wizard

2014-11-05T21:05:48Z

Admin: Created page with "The Starfish ETL System Backup Wizard will walk you through a series of steps to create a complete backup framework for any system. The System Backup Wizard does not actually..."

The Starfish ETL System Backup Wizard will walk you through a series of steps to create a complete backup framework for any system. The System Backup Wizard does not actually back up any data during this process, it only creates the tables and jobs to do so. When the wizard has completed, then you must run the jobs to backup the actual data.

You will first have to verify your Origin and Destination connections for compatibility. The connections for the currently selected Job will be used. The wizard works with any Origin connection type. The Destination must be of a type which support SQL statements (typically any current RDBMS).

When you complete the wizard, it will automatically:

*Create Tables inside of your backup database to match the schema or your Origin tables.
*Create Data maps for each table to back them up.

It may be necessary for you to manually alter the generated table definitions and maps to fit your needs.

Follow the steps below:

1. Create a new job. Set up the Origin & Destination. Your origin query/table selected does not matter in the wizard, but will be necessary to populate to get a valid connection.

2. Start the wizard by clicking the “System Backup Wizard” button on the General tab.

[[file:BackupWizardButton.png]]

3. '''Welcome to the System Backup Wizard''' Read the introduction & click Next.

[[file:BackupWizard1.png]]

4. '''Connector Compatibility Validation''' Next your connections will be validated. If your connections test ok and your destination is of a compatible type, you will be allowed to proceed.

[[file:BackupWizard2.png]]

5. '''Select Origin Tables to include in Backup Scripts''' You will be presented with a check list box with all of the tables in your Origin. Select any table(s) that you will want to include in your backup.

[[file:BackupWizard3.png]]

6. '''Table Creation Settings'''

[[file:BackupWizard4.png]]

{| class="wikitable"
! Option
! Description
|-
| Generate matching schema
| Uncheck this option to skip this step during the wizard (if you already have created the necessary tables)
|-
| Table Prefix
| Will prefix each table name with the value supplied (ie: prefix “bak_” for table “account” will become “bak_account”.
|-
| Default Type
| If a data type is not given by the connector or cannot be resolved through the data_type.map translation file, this value will be used.
|-
| Default Length
| If a type is specified, but not a length this value will be used.
|-
| Maximum Length
| If a value is supplied, and the length specified by the Origin is greater than this number, the length will be changed to this value.
|-
| Change if Maximum Length is exceeded
| If the length specified by the Origin is greater than the Maximum Length, the field data type is changed to this.
|-
| Drop Existing Tables
| Before a table is created by the Wizard a Drop Table command will be issued.
|}

7. '''Map Generation Settings''' Fill in the settings for how you would like your jobs created.

{| class="wikitable"
! Option
! Description
|-
| Generate Jobs
| Uncheck this option to skip this step during the wizard.
|-
| Job Name
| This value will be used when creating each job’s name. The “%tablename” variable will be replaced with the actual table name when the wizard runs.
|-
| Stage Type
| One stage will be created for each job, with each field mapped directly across. This specifies that Stage Type: Insert, Update, Delete, Update/Insert.
|-
| Guess Job’s Primary Key for Match.
| Valid only for Update, Delete, Update/Insert Stage Types. Attempts to place the Match checkbox on the table’s Primary Key.

Here is the logic used for each connector:

Microsoft CRM: The Primary Key field is retrieved through the Metadata API for each table.

NetSuite: The "internalID" field will be used for each table.

Sage CRM: The Table ID will be using matching format (tableprefix + "_" + tablename + "ID") ie: comp_CompanyID

Salesforce: The "Id" field will be used for each table.

SData: The "Key" field will be used for each table.

SQL Server: First the table is evaluated for a Primary Key. If one does not exist then Unique Indexes are used. If there are no Unique Indexes, then regular indexed fields are used.

SugarCRM: The "id" field will be used for each table.
|-
| Chain all Jobs together
| All jobs will be chained together to run after another.
|}

8. '''Last Run Settings'''

This option will create the following:
*An additional table in your Destination database to track individual job last run dates
*A default filter on your Origin query to only select data from the last run date
*Logic to look up this last run date, and save/overwrite it when the job finishes

Then press Start to begin.

[[file:BackupWizard6.png]]

{| class="wikitable"
! Option
! Description
|-
| Only pull data changed from last run
| Check this option to process this step during the wizard.
|-
| Last Run Table
| Name of the table inside your Destination where the Last Run Dates for each job will be stored.
|-
| Default Filter
| For SQL-based connectors, will be appended as a WHERE clause to Origin query; otherwise will be populated as the Origin criteria box.
|-
| Default Format
| Date format to use in in filter/criteria.
|-
| Initial Date
| Date to initially populate in the Last Run table for each job. The first time the job is run, it will pull data starting from this date based on the filter clause.
|}

9. '''Backup Wizard is now Generating''' Please wait while each table & job is created

[[file:BackupWizard7.png]]

10. '''System Backup Wizard Complete''' You may click the link to view the log file generated, or open it directly from the Starfish Admin path, file named systembackup.log.

[[file:BackupWizard8.png]]

File:BackupWizard8.png

2014-11-05T21:05:42Z

Admin:

File:BackupWizard7.png

2014-11-05T21:05:02Z

Admin:

File:BackupWizard6.png

2014-11-05T21:03:31Z

Admin:

File:BackupWizard4.png

2014-11-05T20:59:19Z

Admin:

File:BackupWizard3.png

2014-11-05T20:58:50Z

Admin:

File:BackupWizard2.png

2014-11-05T20:57:58Z

Admin:

File:BackupWizard1.png

2014-11-05T20:56:14Z

Admin:

File:BackupWizardButton.png

2014-11-05T20:55:36Z

Admin:

Starfish Updater

2014-11-05T20:54:07Z

Admin:

File:Updateinprogress.png

2014-11-05T20:53:50Z

Admin:

Starfish Updater

2014-11-05T20:53:48Z

Admin: Created page with "Starfish ETL™ has a built-in updater. To start, click the Starfish button and select “Check for Updates” or launch StarfishUpdater.exe directly from within the Starfish..."

Starfish ETL™ has a built-in updater. To start, click the Starfish button and select “Check for Updates” or launch StarfishUpdater.exe directly from within the Starfish Admin installation path.

[[file:Checkforupdates.png]]

The first time the Updater is run, you'll have to supply the paths to the Starfish ETL™ components. If your Starfish Engine is installed on another server, UNC paths are supported. If you don’t have a certain component installed (such as the Scheduler), you may leave the path empty.

[[file:Updater.png]]

Now you may click the “Look for Updates” button. If the updater finds out of date or missing files, the “Update Now” button will become available. (Files are compared by MD5 hash) Before proceeding, it is recommended that you close out Starfish Admin and shut down the Starfish Scheduler service if running. Now you may press the “Update Now” button. This will download and replace the necessary files from Starfish ETL™ servers.

[[file:Updateinprogress.png]]

File:Updater.png

2014-11-05T20:53:18Z

Admin:

File:Checkforupdates.png

2014-11-05T20:53:05Z

Admin:

Installation

2014-11-05T20:51:44Z

Admin: /* Installation Instructions */

Cross-reference (Xref)

2014-11-05T20:48:57Z

Admin: Created page with "Starfish ETL has built-in methods for maintaining relationships between different systems. Put simply, you will be storing an ID from your Origin, and the new related ID in yo..."

Starfish ETL has built-in methods for maintaining relationships between different systems. Put simply, you will be storing an ID from your Origin, and the new related ID in your destination for the record.

For an example, if you are migrating Company records from one system to another, and later in the process you’ll also be importing Contacts that will be related to these companies you’ll need a method to select which company the contact belongs to. One method would simply be to store the Old (Origin) primary company ID somewhere inside of the new company table on your destination. Then you could use a SQL/SmartLookup and pull down the ID by a filter clause. This is perfectly acceptable; however you'll take a performance hit because for each row you’ll have to perform another lookup operation. Using Xref functions, these ID relationships are stored in memory, thus making the lookup operation instantaneous – speeding up your process. The idea will be to write out the ID relationships for accounts while that job is running. Then, when the Contacts job runs it can initialize and read from this list to quickly & easily relate to the correct Company.

Internally, Xref Lists are stored in your StarfishEngine\Xref folder as simple text files with an .ids file extension. You may modify these files by hand if necessary.

=Xref List Initialization=

To begin, you must initialize an Xref List before you can read or write to it. This is done on a per-job basis through the Xref Lists button on the Mapping tab.

[[file:Xref1.png]]

Write to a new list to store a relationship between an Origin Primary Key and the Destination Primary Key. The checkbox indicated whether the list should be appended to. When it is written to, by default, the entire list will be blanked and written to as new when the job is run. Check to box to instead append to the end of the existing list. (for instance if you need to run multiple jobs which write to the same list, or need to run the job multiple times for some reason).

Reading from an existing list will allow you to retrieve the “New ID” for a system, given an “Old ID”. The drop-down on the Read side gets a list of Xref Lists that are available on the server.

Rather than using the built-in screens for Xref List Initializations, you may also use the XrefInitRead() and XrefInitWrite() VBScript functions.

=Xref Writes=

To write to an initialized Xref list, you may use the Exec Operation “Xref Write”. Select the list from the Xref List drop-down. Then supply a value or variable to use as the Origin/Old ID, and one for the Destination/New ID.

[[file:Xref2.png]]

Alternatively, you can use the XrefWrite() VBScript function to write to an Xref list.

=Xref Reads=

To read from an initialized Xref list, you may use the “Xref Read” function field in your mapping.

[[file:Xref3.png]]

Alternatively, you can use the XrefRead() VBScript function to read from an Xref List.

=File Format=

This data is stored in a series of text files (by default) in the C:\inetpub\wwwroot\StarfishEngine\Xref\ folder, with the .ids extension. These files can be edited by using using a text editor. The format is simply OLD ID|NEW ID (pipe character separates the ID's) with one set on each line.

File:Xref3.png

2014-11-05T20:48:14Z

Admin:

File:Xref2.png

2014-11-05T20:47:55Z

Admin:

File:Xref1.png

2014-11-05T20:46:47Z

Admin:

General Help

2014-11-05T20:45:41Z

Admin:

*[[Create a New Job]]
*[[Copying a Job]]
*[[Scheduling Jobs]]
*[[Previewing Jobs]]
*[[Running Jobs]]
**[[Run Options]]
**[[Using Multi-threading]]
*[[Saving Jobs]]

=Connections=
*[[Configuring the Origin]]
*[[Configuring the Destination]]
*[[Using Transactions]]

=Mapping=
*[[Creating a Stage]]
*[[Stage Options]]
*[[Mapping Screen Overview]]
*[[Straight Field-to-Field Mapping]]
*[[Function Field Mapping]]
**[[SQL Lookups]]
**[[Scripted Value]]
*[[Exec Before/After Operations]]
**[[Action Types]]
**[[Execute When]]
*[[Inserting Variables]]
*[[Starfish Scripting Class]]
*[[Cross-reference (Xref)]]

Inserting Variables

2014-11-05T20:39:31Z

Admin: Created page with "Variables that have been created during an Exec Before/After Operation can be inserted as the end-result value in any kind of Function Field. Place your cursor inside the text..."

Variables that have been created during an Exec Before/After Operation can be inserted as the end-result value in any kind of Function Field. Place your cursor inside the text box, and click the “Insert Variable” link.

[[file:Insertvarlink.png]]

From this list, you can choose a User-Defined variable, any Origin field, any Destination field (that comes before it in the sort order, and that has been assigned a value), or a Special Character. At run-time, the Starfish Engine will “replace” the variable code with the actual value for the current Origin row.

[[file:Insertvarwindow.png]]

File:Insertvarwindow.png

2014-11-05T20:39:07Z

Admin:

File:Insertvarlink.png

2014-11-05T20:38:51Z

Admin:

Action Types

2014-11-05T20:37:25Z

Admin: Created page with "The Action Type defines what type of operation will be performed. Variable-type actions create a User-defined variable for use through the Job. file:Actiontypes.png {| c..."

The Action Type defines what type of operation will be performed. Variable-type actions create a User-defined variable for use through the Job.

[[file:Actiontypes.png]]

{| class="wikitable"
! Action Type
! Description
|-
| Variable/SQL
| Set a variable through the execution of a SQL SELECT statement run against the Destination database. Set the variable value equal to the first column of the first row returned by the statement.
|-
| Variable/VBScript
| Set a variable through the execution of a custom VBScript function. A shell function is provided called “ScriptedVariable”, the value of the variable will be what is returned out of the function.
|-
| Execute SQL
| Execute any kind of statement against the Destination database. Useful for calling DELETE or UPDATE statements before or after a job for cleanup purposes.
|-
| VBScript Procedure
| Execute any statements desired in a VBScript Sub. A shell sub called “VBScriptProcedure” is provided. Any kind of VBScript processing can be called here. Useful for before-job file manipulation, or any kind of pre- or post-processing.
|}

File:Actiontypes.png

2014-11-05T20:36:57Z

Admin:

General Help

2014-11-05T20:08:19Z

Admin: /* Mapping */

*[[Create a New Job]]
*[[Copying a Job]]
*[[Scheduling Jobs]]
*[[Previewing Jobs]]
*[[Running Jobs]]
**[[Run Options]]
**[[Using Multi-threading]]
*[[Saving Jobs]]

=Connections=
*[[Configuring the Origin]]
*[[Configuring the Destination]]
*[[Using Transactions]]

=Mapping=
*[[Creating a Stage]]
*[[Stage Options]]
*[[Mapping Screen Overview]]
*[[Straight Field-to-Field Mapping]]
*[[Function Field Mapping]]
**[[SQL Lookups]]
**[[Scripted Value]]
*[[Exec Before/After Operations]]
**[[Action Types]]
**[[Execute When]]
*[[Inserting Variables]]
*[[Scripting Starfish Class]]
*[[Cross-reference (Xref)]]

Exec Before/After Operations

2014-11-05T20:06:49Z

Admin: Created page with "Any number of functional operations can be performed before or after a Job is processed. To set up the operations, click the Add/Edit link for Exec Before (on the top of the s..."

Any number of functional operations can be performed before or after a Job is processed. To set up the operations, click the Add/Edit link for Exec Before (on the top of the screen) or Exec After (on the bottom of the screen)

[[file:Beforeoperations.png]]

Enter a meaningful name, select the Action Type, assign a variable name where necessary, set the Exec When option, and configure the Action.

File:Beforeoperations.png

2014-11-05T20:06:35Z

Admin:

Scripted Value

2014-11-05T20:05:16Z

Admin:

Scripted Value fields allow for extreme flexibility when assigned values to a Destination field. Basically, any function that can be performed with VB Script can be performed within a Scripted Value field. Variables can also be referenced from within the script.

Below are a few examples of how Scripted Value fields can be used.

''Samples''
{| class="wikitable"
! Return the Current Date/Time
! Return the left-most 5 characters from “ZIPCODE” Origin field
|-
| Function ScriptedField

ScriptedField = Now()

End Function
| Function ScriptedField

Dim var

var = "@@ORG:ZIPCODE@@"

ScriptedField = Left(var,5)

End Function
|-
! Trim an Origin field
! Return different values, based on a Variable value
|-
| Function ScriptedField

Dim var

var = "@@ORG:TYPE@@"

ScriptedField = Trim(var)

End Function
| Function ScriptedField

If "@@VAR:vMyVariable@@" = "" Then

ScriptedField = "Value 1"

Else

ScriptedField = "Value 2"

End If

End Function
|}

Press the [[file:Check.png]] button to test the results of your script. Keep in mind, this will not replace variable values.

[[file:Scriptedvalue.png]]