Auto Export Import Tool

Parent Previous Next






Auto Export Import Tool



The installer file for Auto Import/Export can be found within the SMSTurbo directory on your computer at c:\programfiles (x86)\smsturbo\ AutoImportExportInstall\setup or it can be supplied by CIS.

The installer will run and prompt you to choose your installation method. If you do not change anything both Import and Export Services will be installed on your computer. You may choose to install only Import or Export.

Once the software has been installed you will have (depending on your install options) two icons to setup Import and Export.

(Note: Always use the installer program for the auto export/import tool to ensure that the file versions are correct. If not the application may not function correctly).

You will also have the services for Import and Export installed but not running.

Note: The services are installed but not running. Once you have configured the services you must start them from the services applet in Windows or simply restart the computer and they will start automatically(Control panel, administrative tools, services).

Automated Export

Automated Export's function is to export data from the source database to a file and transport that file to the computer that the Import Service is running on.

Figure 1 Automated Export Setup (File Transport)

The common setup fields on this screen are the SQL Server fields:

1. SQL Server, this will be where you enter the name of the SQL Server that you are exporting from.

2. SQL Database, the SQL database name that will be used to get the data to export.

3. SQL User and password are where you will enter the User/Password to access SQL. The password will be encrypted for storage and only visible on this screen.

Transport Type

1. Tickets: This will export Tickets and Supporting Data (customer accounts, trucks and the like to the destination service,

if not already present there.

2. Use the Export Ticket Age setting to define how old in minutes a ticket must be before it is exported.

3. Tickets in Package  This limits the number of tickets in a zip file. This will allow customers to create separate smaller zip files

which should help to meet mail attachment size limits when sending the zip files out.

(Note: Once a ticket is exported the actual ticket will have a tag of "Exported" written on it in SMSTurbo.)

4. Ticket Export time Overrides ticket Export Age Set this value to the current time to do a one time ticket export, or

to schedule the export once per day.

3. Supporting Data: This will export only supporting data to the destination service. Use the Supporting Data Export Time field to set either a specified interval in minutes or times separated by commas that you want the data to be exported. At specified intervals

an update file will be sent regardless of whether any changes occurred. Please note that when a change to a rate is processed that

change can only occur if the receiving data base already has that customer and order.  (the customer and order did not exist on

the receiving system at the time the rate change was made. To fix simply make an update to the customer maintenance screen  and order maintenance screen. This will push those 2 items over. Then change the rate to get that pushed over.  Any change to any of the maintenance screens gets the entire screen pushed over.

First we will cover the setup fields for Exporting tickets.

1. Path to save export. This must be a path on the computer that the Import Service is running on. You must have Write access to this folder. Use the "..." button to select a path for exporting.

2. User Impersonation: This allows the service to act on behalf of a user on this system or the remote system to allow the service to move the files from Export side to Import Side.

a. User Name: This is the Windows User that will be used.

b. Password: The Users password. (Encrypted when stored and not visible on the screen)

c. Retype Password: Password verification, this must match the Password mentioned above. (Not Stored).

d. Domain: The users domain, this can be blank if a Windows Domain is not being used.

Logging

LogFile Name and Path:  Set the path and name of the log file to log auto export/import events.

Days to Keep Logs:  Set the number of days to retain the logs:  

Service Console

As part of the installation of the tool a file called ServiceConsole.exe is available in the c:\program files (x86)\SMSTurboAutoImportExport\ folder.

Navigate to this folder and double left click this file to launch the monitor tool.

An shortcut will be created on the desktop to launch this tool, it is called "AIE Service Console"

As the service runs events will be shown occuring here.

When exporting Supporting Data the setup screen changes slightly to show some alternate settings.shown here:

When exporting Supporting Data it is possible that you might wish to send this data to multiple locations. The Pick Paths button opens a dialog (shown below)

where these paths may be entered. Note: You must have at least one path.

This dialog allows you to choose one or more paths for the support data to be moved to. Use the Add new and Delete buttons to add or remove paths.

Note: Mapped drives do not work. UNC paths are OK.

Note: All errors regardless of email settings are logged to the Windows event log.

Automated Import

Automated Import is designed to import files created by the Automated Export Service. The import service has the same transport method as the Export Service and should be configured to match its sister instance of Automated Export.

Some key notes to Automated Import.

It must be running on the computer where the Export files are located when running.

The file system monitor cannot monitor remote drives or mapped drives of any type.

Figure 1 Automated Import (File Transport)

The common setup fields on this screen are the SQL Server fields:

1. SQL Server, this will be where you enter the name of the SQL Server that you are importing into.

2. SQL Database, the SQL database name that will be used to put the data into.

3. SQL User and password are where you will enter the User/Password to access SQL. The password will be encrypted for storage and only visible on this screen.

Transport Type

Tickets or supporting data.

If Update supporting data is unchecked then only new records will be added to the database. Updates will be ignored from the remote site.

If Tickets is checked then tickets will be exported.

If Don't mark tics as imported is checked then it enables the import side to continue to edit tickets without seeing the blue banner saying EXPORTED on the ticket.

File Transport Setup

1. Path to monitor for Import. This path must be a path located on the physical disk of the computer running the Import Service. Use the "..." button to select a path for exporting.

Note: This path must be shared for the Export Service(s) to be able to write to the folder. The users would at least need Write access to this folder remotely and need Write/Delete access on the local computer.

Note1: All errors regardless of email settings are logged to the Windows Event Log.

Note2: Only data that has been added or updated based on the Create and Modified dates in the record are sent.

Email Setup and Use

Email is used to send notifications of events for the Import and Export Services. Most of these messages are warnings and errors. However for troubleshooting and setup it can be made to send emails on successful events as well.

Note: All errors regardless of email settings are logged to the Windows Event Log.

Setting up email:

1. Your email address: This will be your email address that you use to send email.

2. Display Name: This will be the name that will be on the email.

3. Auto Configure: If you are using GMail, Hotmail, Live.Com or Yahoo you can click this to fill in the remaining fields.

4. SMTP Server: The mail server that will be sending your mail

5. SMTP Port: The port on which the SMTP Server operates

6. Enable SSL: Enables SSL for the SMTP Server.

7. Your SMTP User: Generally your email address or a variation of it.

8. SMTP Password: The password for your SMTP User. This is encrypted when stored.

9. Retype Password: To verify that you typed the password correctly. This is not stored.

10. Distribution List: This is the list (separated by semi colons) of email addresses that will be emailed.

11. Check to Enable: When checked and email is setup the Services will send emails on errors.

12. Send Email on Transport: This is useful for troubleshooting Export/Import issues. When checked an email is sent: On Export, On Send, On Import add to queue and finally on Import to database.

Note: These settings are the same for Import and Export.

Some common questions that may be asked are:

Q. What happens if the service gets the same ticket number twice?

A. The first one wins, the second will be ignored

Q. What happens when a customer is changed from one ticket to the next?

A. The customer record will be updated with the second set of data for that customer.

Q. What happens if the import fails?

A. In FILE mode the file is renamed with "_FAILED" on the end of its name. What ever records where imported remain up until the record that caused the error. You may import this file in SMSTurbo to see the actual error.

Q. If the import service isn't running will it still get files?

A. No.

Q. Where do I see Import/Export Errors?

A. In the Windows Event Log Under Application.

Q. What happens if the Export fails to write the file to the remote location?

A. The export is aborted and it will try again on the next time cycle.

Q. The export or Import Service Start then Stop, What is wrong?

A. It could be one of several things, some of which are:

1.The remote location is not available.

2. The Option has not been purchased or enabled for AutoImportExport.

4. The service is not fully configured

All of these errors and any others will be reported in the Windows Event Log.

Tickets

The Tickets export extracts tickets from the database based on two things.

1. The Ticket status, the ticket must be a closed and completed ticket.

2. The Auto Export Flag, Each ticket after it is exported is marked so that it will not be exported again.

The Export file is created and Compressed the same way and format as the Tickets Export in SMSTurbo.

The export contains all information needed to add a ticket to the database. For example Customer and Material records for that ticket.

The import will read these files and import them into the database.

Supporting Data

Supporting data consists of

Customers/Vendors

Trucks

Trailers

Containers

Materials

Orders

Rates

Haulers

DispatchInfo

Note:  SMS User accounts are NOT Exported via the auto export import tool.

These tables are exported in their entirety every time. This is to ensure that the remote locations have all the new data and changed data that they need to enter Tickets into the system.

Supporting data is generally for the Main site to export to the remote sites. This is to maintain one location for entering new data (other than tickets). The remote sites would in turn send back Ticket records. This is further discussed in Transport Methods.

Transport Method

The only currently supported Transport Method is File. Dropbox is supported rather than Windows file shares because the autoimport feature will see a file copied onto the share

before the file is finished copying fully over, which can cause it to fail. Dropbox only shows files at a location becasue they have completely copied there.

File uses the File System to move export files to the Import computer via Dropbox..

The following shows the File transport Method in a diagram with the above concept in mind. There are many ways in which Import and Export could be used in combination

this is just one way. For more information on how these can work please contact sales or support.

FILE


Technical Notes and Troubleshooting

1. We have seen that a mismatch of the versions on the files SMSSQLServer.dll and SMSTurbocommon.dll in the

c:\programfiles (x86)\SMSTurboAutoImportExport folder from the dlls in the c:\programfiles (x86\SMSTurbo folder can

cause the services to stop intermittently. If the SMSTurbo software is updated make sure the dlls in the SMSTurboAutoImportExport

folder are also updated.  It is possible to copy the dlls from the SMSTurbo install folder over to the SMSTurboAutoImport Export folder.

2. The services for autoimport and autoexport both run on the importing machine and the exporting machine. Make sure all 4 are running.

If they are halting for some reason check the event viewer for error messages.  An "access denied" message may mean that the logged in Windows

user does not have write privileges to one of the log folders specified under the setup screen for auto import/export.

3. A common problem is that on the importing machine the services stopped for some reason. Perhaps the machine was restarted in a way

that did not allow the services to restart or number one (above) occurred on the exporting machine.  The exporting machine may continue to write to

the drop box location and create a rather large zip file. this may result in an out of memory error. Performing a manual import may fix this issue.

4. All machines should be logged into the same Drop box accounts.to allow the same folder sets to be reviewed. The paths to the Drop box folders

should be local paths on the respective machines, which in turn will update the local Drop box folders on every machine, logged into the same account.

Drop box always needs to be running in the computers system tray and mapped to the correct folders for the export file.

5. Sometimes an issue occurs that the SMS user can no longer edit exported tickets. This can be caused because a setting on the importing machine running under autoimport setup on the desktop, needed to be changed. Check the check box for "Don't mark tickets imported"  In this situation the tickets already

exported had to changed in SQL.  In SQL, under the smsdata db, under dbo.ticket, query for all the tickets that have the autoexportflag set to 'Y'. After locating those tickets use this command to set the flag to 'N'. Update ticket set autoexportflag = 'n' where autoexportflag = 'y'

Now the customer will be able to edit all of the tickets that were marked and not editable. .