April 2020 - Page 3 of 5 - File transfer tool from Limagito

26 Apr
How-To use ODBC DSN with SQL as Source or Destination
On request we’ve added ODBC as connection option for SQL as Source or Destination in version v2020.4.25.0. Our ODBC bridge driver supports Level 2, 3 drivers. It requires the x86 or x64 ODBC driver to be installed according to ODBC specification.
First you’ll need to setup a System DSN (Data Source Name) that will be used in the Database Connection Setup of the SQL Source or Destination Setup of our File Mover.
Configure DSN using the ODBC Administrator control panel. Important:
When you are using the 64-bit version of LimagitoX you’ll need the ODBC Data Sources (64-bit) App.
When you are using the 32-bit version of LimagitoX you’ll need the ODBC Data Sources (32-bit) App.
We’ll be using the 64-bit version of the ODBC Data Source Administrator . Select the System DSN Tab and click < Add >.
In this example we’re going to connect to a MS SQL Server Express (v2014). We’ll be using the (already installed) ODBC Driver 11 for SQL Server. Select and click < Finish >.
( https://web.synametrics.com/odbcdrivervendors.htm )
Please choose the Name carefully. This DSN Name will be used later in the SQL Database Connection Setup of our file Mover. The Server is the ‘full name’ of the SQL Server we are going to connect to (Hostname \ SQL Instance). Click < Next >.
Hint: If you don’t know the Server name, please start the ‘Microsoft SQL Server Management Studio’.
We’ll be using SQL Server authentication. Please enter Login ID (Username) and Password. We’ll need this during the DSN setup so we can select the default database and test the connection. The Password will not be stored in the DSN. You ‘ll need to add this in the SQL Database Connection Setup of our File Mover later.
We changed the default database to the one we’ll be using in our File Mover. Click < Next >.
Click < Finish >.
Click < Test Data Source >
Result should show: TESTS COMPLETED SUCCESSFULLY!.
Click < OK >.
Click < OK >.
The new System DSN is ready to be used within LimagitoX File Mover. Click < OK >.
In the SQL as Source or Destination Setup of LimagitoX File Mover you’ll find ODBC as Database Vendor option (Database Tab).
Set the following fields:
DSN (Name of the existing system DSN to use for connecting)
Database
Username
Password
Click < Connect > to test the ODBC connection.
Check the Log window for the connection result. Log shows the different table names of the database so the connection is OK.
If you need help, please let us know.
Best Regards,
Limagito Team
By Limagito-Team SQL dsn, odbc, SQL
18 Apr
How-To add Command Line as Destination in LimagitoX File Mover
In version v2020.4.18.0 we’ve added an ‘Execute Command as Destination’ option.
Open Destinations and <Add CMD>.
For Command Lines tools please always use the ‘Command Line’ field. Leave the ‘Application Name’ field empty.
This field will (almost) always start with: CMD /C
In this basic example we have added Copy and some parameters
%SFP%SFN = Source File Path and Source File Name
%DFP%DFN = Destination File Path and Destination File Name
The %DFP will be filled with the ‘Destination Directory’ from the Command Setup (it will contain a \ at the end).
If we don’t use the file renaming option then %DFN will be the same as %SFN.
When we trigger the Moving Rule, we get the following result in the RunTime Log. Did you notice the translation of the parameters.
If you need any help, please let us know.
Regards,
Limagito Team
By Limagito-Team Command CMD, command

17 Apr

How-To use a csv file as filter and destination path in LimagitoX File Mover

Another example of how Pascal Script can be used. In this how-to we’ll explain you how the content of a csv file can be used to filter the source file and at the same time this csv file will be used to set the destination path.

In this example we use C:\Test\Demo as base folder. It contains 3 subfolders which will be used by the Moving Rule.

C:\Test\Demo\Csv : contains the Csv file which we will scan for its content. The content will give us the file filter and destination path.
C:\Test\Demo\In : folder will be used as source of the moving rule
C:\Test\Demo\Out : folder will be used as destination path. We’ll receive this info from the csv file.

In the Csv subfolder we created an excel file. First column contains the filename filter. We’ll search for files which contain this string in its filename. The second column is used for the destination path when this file is found.

Next we did an export of this excel file to csv. We need this cvs file for our Pascal Script.

Content of the Source Folder:

Select this folder in the Source Setup (WIN).

Setup of the File Filter. Important, select RegEx Include filter and add %VSB as filter.

Open the Pascal Script Setup:

Add the following ‘On Rule Begin’ Pascal Script. Don’t forget to adjust the ctCsvFile constant (depends on your setup).

Var
  iList: Integer;
  tmpList: TStringList;
  tmpEntry: String;
  tmpName: String;
  tmpCount: Integer;
  tmpPos: Integer;
Const
  ctCsvFile = 'C:\Test\Demo\Csv\List.csv';
  ctCsvSep = ';';
Begin
  psExitCode:= 0;
  psVSA := '';
  psVSB := '';
  // ... add your code here
  Try
    tmpList := TStringList.Create;
    Try
      tmpList.LoadFromFile(ctCsvFile);
      If tmpList.Count > 0 Then
      Begin
        tmpCount := tmpList.Count - 1;
        For iList := 0 to tmpCount Do
        Begin
          tmpEntry := Trim(tmpList.Strings[iList]);
          tmpPos := Pos(ctCsvSep, tmpEntry);
          If tmpPos <> 0 Then
          Begin
            tmpName := Copy(tmpEntry, 1, tmpPos-1);
            If iList = tmpCount Then
              psVSB := psVSB + '.*' + tmpName + '.*'
            Else
              psVSB := psVSB + '.*' + tmpName + '.*|'
          End;
        End;
        psVSA := tmpList.CommaText;
        // Debug Info
        psLogWrite(1, '', 'psVSA: ' + psVSA);
        psLogWrite(1, '', 'psVSB: ' + psVSB);
        // Set psExitCode
        If (psVSA <> '') And (psVSB <> '') Then
          psExitCode := 1;
      End;
    Except
      psLogWrite(1, '', 'Load Script File Exception');
    End;
  Finally
    tmpList.Free;
  End;
End.

Add the following ‘On Destination’ Pascal Script.

Var
  iList: Integer;
  tmpList: TStringList;
  tmpEntry: String;
  tmpName: String;
  tmpPath: String;
  tmpCount: Integer;
  tmpPos: Integer;
Const
  ctCsvSep = ';';
Begin
  psExitCode:= 0;
  psVSC := '';
  // ... add your code here
  Try
    tmpList := TStringList.Create;
    Try
      tmpList.CommaText := psVSA;
      If tmpList.Count > 0 Then
      Begin
        tmpCount := tmpList.Count - 1;
        For iList := 0 to tmpCount Do
        Begin
          tmpEntry := Trim(tmpList.Strings[iList]);
          tmpPos := Pos(ctCsvSep, tmpEntry);
          If tmpPos <> 0 Then
          Begin
            tmpName := Copy(tmpEntry, 1, tmpPos-1);
            If Pos(tmpName, psFileName) <> 0 Then
            begin
              tmpPath := Copy(tmpEntry, tmpPos+1, length(tmpEntry)-tmpPos);
              If tmpPath <> '' Then
              Begin
                psVSC := tmpPath;
                Break;
              End;
            End;
          End;
        End;
        // Debug Info
        psLogWrite(1, '', 'psVSC: ' + psVSC);
        // Set psExitCode
        If (psVSC <> '') Then
          psExitCode := 1
        Else
          psLogWrite(1, '', 'Could not find a path for: ' + psFileName);
      End;
    Except
      psLogWrite(1, '', 'Find Destination Path Exception');
    End;
  Finally
    tmpList.Free;
  End;
End.