Automating your CAD Export and SFTP Transfer

Once we have fine tuned your CAD extract, Corti can help to automate data transfer for you

Automating File Extract from SQL Server Management Studio

First, we will look to automate extracting your data from your CAD system and saving this as a file to a local location. The following section includes instructions to do so:

1. Open SQL Server Management Studio and connect to the database where you want to create the export job.

2. Go to "SQL Server Agent" > “Jobs” in Object Explorer.

3. Right-click on "Jobs" and select "New Job".

4. In the "New Job" dialog box, give the job a name.

5. Click on the "Steps" page and click "New" to add a new step.

a. In the "New Job Step" dialog box, give the step a name with Type as “Transact-SQL script (T-SQL)” from the dropdown list. Also select the database you want to run the query on.

b. In the "Command" field, enter the SQL query that you want to run, including the command to save the CSV file to a path of your choosing. The following example takes your SQL statement and creates a file name that will be appended with the previous day’s date (for example CADExport_20230131). Assuming your task runs everyday after midnight, it will name the file to match the data:

/** Get previous day date in the format yyyymmdd **/
DECLARE @yesterday VARCHAR(8) = CONVERT(VARCHAR(8), DATEADD(DAY, -1, GETDATE()), 112)
    
/** Set the export path and file name **/
DECLARE @exportPath NVARCHAR(100) = 'D:\Corti\'
DECLARE @fileName NVARCHAR(100) = 'CADExport_' + @yesterday + '.csv'
DECLARE @filePath NVARCHAR(200) = @exportPath + @fileName 
DECLARE @cmd VARCHAR(1000)
DECLARE @mainSelect VARCHAR(1000)

/** Paste select statement between single quotes below. Escape any single quotes. Remove carriage returns **/
SET @mainSelect = ''

/** Run sql statement and write output to csv file **/
SET @cmd = 'bcp "' + @mainSelect + '" queryout "' + @filePath + '" -T -c -t "|"'
EXEC master..xp_cmdshell @cmd

Note: in the above code, you will want to replace C:\Exports\CADExport with the appropriate filepath for your setup.

c. Click "OK" to save the job step.

6. Click "OK" again to save the job.

7. Click on the “Schedules” page and click “New”.

a. In the “New Job Schedule” dialog box, give the schedule a name and set the schedule for which you want the job to run (e.g., daily at a specific time).

b. Click "OK" to save the schedule.

8. Click "OK" again to save the job.

9. The SQL Server Agent job will now run on the schedule that you specified and export the data to a CSV file.
Once we have confirmed that the necessary pieces of data are being received from your CAD and/or Call recorder - the next step is to automate the export. We have an example script below, but if you have questions or issues, please reach out to your CSM.

Automating File Transfer via SFTP

Once we have the file creation automated, we will want to automate the transfer of the file that is created each day. The below anonymized example script will:

1. Create the connection to the SFTP drive
2. Create a dated file in the SFTP drive
3. Copy the files from a source folder to the dated file
4. Delete the files from the source folder

Important Notes:

• You will need to add the Corti-provided appropriate identifying information for the SFTP drive (e.g. HostName, private key, etc.)
• Corti will also be able to provide the dll file used by the below script. Corti can drop this file on your customer specific SFTP server. Please let your CSM know if this is of interest.
• Ensure that the correct source folder is being used (this is likely set up in the instructions for Automating File Extract from SQL Server Management Studio), Remember that as written the files will be deleted after the script is run
• Use Windows Task Scheduler to schedule the task

try

{

Add-Type -Path "WinSCPnet.dll"

$session = New-Object WinSCP.Session

$sessionOptions = New-Object WinSCP.SessionOptions

$sessionOptions.Protocol = [WinSCP.Protocol]::Sftp

$sessionOptions.HostName = "api.[customername].motocorti.io"

$sessionOptions.PortNumber = 2222

$sessionOptions.UserName = "corti"

$sessionOptions.Password = ""

$sessionOptions.SshPrivateKeyPath = ""

$sessionOptions.SshHostKeyFingerprint = ""




$today = Get-Date -Format "yyyy-MM-dd"

#$sourceFolder = "F:\NICE\NICE Inform Recorder\Content\audio1\{0}" -f $today

$sourceFolder = "c:\export\" -f $today

$destinationFolder = "input/{0}" -f $today




    try

    {

        # Connect

        $session.Open($sessionOptions)




# Create remote directory

$session.CreateDirectory($destinationFolder)

}

catch

{write-host "Directory Exists" }

try

{

        # Upload files

        $transferOptions = New-Object WinSCP.TransferOptions

        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary




        $transferResult =

            $session.PutFilesToDirectory($sourceFolder, $destinationFolder, "*", $False, $transferOptions)




        # Throw on any error

        $transferResult.Check()




        # Print results

        foreach ($transfer in $transferResult.Transfers)

        {

            Write-Host "Upload of $($transfer.FileName) succeeded"

            Remove-Item -Path $transfer.FileName -Force

        }

    }

    finally

    {

        # Disconnect, clean up

        $session.Dispose()

    }




    exit 0

}

catch

{

    Write-Host "Error: $($_.Exception.Message)"

    exit 1

}