Information	5/28/2021 2:53:47 PM	Preparing to execute: SSEAUTO_Poll SFTP
Information	5/28/2021 2:53:47 PM	Started
Information	5/28/2021 2:53:47 PM	Executing:SSEAUTO_Poll SFTP
Information	5/28/2021 2:53:47 PM	Executing Set Variable:Set Environment
Information	5/28/2021 2:53:47 PM	Success
Information	5/28/2021 2:53:47 PM	Executing Script:Setup
Information	5/28/2021 2:53:47 PM	import os
import uuid
import etltools





cdt = etltools.GetVariable('<datetime>')
mdy = cdt[:8]

guidx = uuid.uuid4().hex
etltools.SetVariable('<sessionguid>',guidx)
etltools.SetVariable('<runmdy>',mdy)

Result.Value = 1
Information	5/28/2021 2:53:47 PM	Executing:Setup
Information	5/28/2021 2:53:47 PM	Success
Information	5/28/2021 2:53:47 PM	Executing Loop: Loop CONFIG_SRC
Information	5/28/2021 2:53:47 PM	Using Following Sql: exec [dbo].[tsp_GetConfig_SRC] 'PROD'
Information	5/28/2021 2:53:47 PM	Loop Value: XCLINVRPA_PRD
Information	5/28/2021 2:53:47 PM	Success
Information	5/28/2021 2:53:47 PM	Executing Set Variable:Set CONFIG_SRC
Information	5/28/2021 2:53:47 PM	Success
Information	5/28/2021 2:53:47 PM	Executing Set Variable:Config Params
Information	5/28/2021 2:53:47 PM	Using Following Sql: exec [dbo].[tsp_GetConfig] 'XCLINVRPA_PRD'  ,'PROD'
Information	5/28/2021 2:53:47 PM	Success
Information	5/28/2021 2:53:47 PM	Executing Sql Script: Clear Staging
Information	5/28/2021 2:53:47 PM	Connection Name: XGS_InvoiceRPA_Tracker_SQL
Information	5/28/2021 2:53:47 PM	Connection Type is: MS Sql Server
Information	5/28/2021 2:53:47 PM	Executing Script:PY sftp_list_to_db
Information	5/28/2021 2:53:47 PM	import  pysftp,pyodbc, os, time
import etltools

def sftp_list(sftp_host, sftp_username, sftp_password, sftp_target, sessionguid, dbstr):
    files = []
    dir_names = []
    un_name = []
    cnopts = pysftp.CnOpts()
    cnopts.hostkeys = None

    try:
        sftp = pysftp.Connection(host=sftp_host, username=sftp_username, password=sftp_password, cnopts=cnopts)

        def store_files_name(fname):
            stat = sftp.lstat(fname)
            f = (
                 os.path.dirname(fname),
                 os.path.basename(fname),
                 time.strftime("%Y-%m-%d %H:%M:%S ",time.gmtime(stat.st_mtime)),
                 sessionguid,
                 target_disk,
                 config_src
            )
            files.append(f)

        def store_dir_name(dirname):
            dir_names.append(dirname)

        def store_other_file_types(name):
            un_name.append(name)

        sftp.walktree(sftp_target,store_files_name,store_dir_name,store_other_file_types,recurse=False)
        sftp.close()

        if files:
           cursor.executemany("""INSERT INTO stg_ftpfiles (sftp_src, sftp_filename, sftp_timestamp, session_guid, location_on_disk, config_src ) VALUES (?, ?, ?, ?, ?, ?)""", files)
           cursor.commit()

        success = 1

    except Exception as ex:
        etltools.WriteToLog(str(ex))
        #print(str(ex))
        success = 0
        cursor.rollback()
    finally:
        sftp.close()
        cursor.close()
        db.close()
    return success


config_src = etltools.GetVariable('<CONFIG_SRC>')
sftp_host = etltools.GetVariable('<SourceSFTPHost>')
sftp_username = etltools.GetVariable('<SourceSFTPUsername>')
sftp_password = etltools.GetVariable('<SourceSFTPPassword>')
sftp_target = etltools.GetVariable('<SourceSFTPPath>')
sessionguid = etltools.GetVariable('<sessionguid>')
db_driver = etltools.GetVariable('<TargetDBDriver>')
db_server = etltools.GetVariable('<TargetDBServer>')
db_database = etltools.GetVariable('<TargetDBDatabase>')
target_disk = etltools.GetVariable('<TargetDisk>')



dbstr = 'DRIVER={' + db_driver + '};SERVER=' + db_server + ';DATABASE=' + db_database + ';Trusted_Connection=yes'
etltools.WriteToLog('dbstr: ' + dbstr)
db = pyodbc.connect(dbstr)
cursor = db.cursor()
cursor.fast_executemany = True

success = sftp_list(sftp_host, sftp_username, sftp_password, sftp_target, sessionguid, dbstr)
etltools.WriteToLog('success: ' + str(success))
Result.Value = success
Information	5/28/2021 2:53:47 PM	Executing:PY sftp_list_to_db
Error	5/28/2021 2:53:47 PM	Following Error happened: ModuleNotFoundError: No module named 'pysftp'
Information	5/28/2021 2:53:48 PM	Executing Set Variable:Set Error Message
Information	5/28/2021 2:53:48 PM	Success
Information	5/28/2021 2:53:48 PM	Sending Email: Send Error Email
Information	5/28/2021 2:53:48 PM	To: CAN.XGS.Server.Support@xerox.com
Information	5/28/2021 2:53:48 PM	Path: C:\DATA\ETLLog\
Information	5/28/2021 2:53:48 PM	Mask: Package_1736917.log
Information	5/28/2021 2:53:48 PM	Path Is Valid: True
Information	5/28/2021 2:53:48 PM	Include Directory: False
Information	5/28/2021 2:53:48 PM	In Sub Folders: False
Information	5/28/2021 2:53:48 PM	Attributes: ReadOnly
Information	5/28/2021 2:53:48 PM	Attribute: Hidden
Information	5/28/2021 2:53:48 PM	Attribute: SysFile
Information	5/28/2021 2:53:48 PM	Attribute: aArchive
Information	5/28/2021 2:53:48 PM	File Age: 0
Information	5/28/2021 2:53:48 PM	Which File: 0
Information	5/28/2021 2:53:48 PM	Attachment: C:\DATA\ETLLog\Package_1736917.log
Error	5/28/2021 2:53:48 PM	Failed to send Email: Send Error Email
Error	5/28/2021 2:53:48 PM	Socket Error # 10013
Access denied.
Information	5/28/2021 2:53:48 PM	Executing Set Variable:EOP
Information	5/28/2021 2:53:48 PM	Success
Information	5/28/2021 2:53:48 PM	Finished