Goanywhere FTP Uploaded Trigger Insert to SQL DB

[weesiong]

Hi All,

Do you have any sample project configuration for below case?

• When file uploaded successfully into FTP Server, use the Trigger call a project, insert the filename into SQL DB.

Thank you.

Best regards,
Wee

[Support_Rick]

This is a utility I created several years back... it should do what you're looking for.

Code: Select all

<project name="Write File Stats"
         mainModule="Main"
         version="2.0"
         logLevel="silent">
		 
	<variable name="trg_filename"
	          value="" />
	<variable name="trg_filesize"
	          value="" />
	<variable name="trg_filetype"
	          value="" />
	<variable name="trg_lastmod"
	          value="" />
	<variable name="trg_trantype"
	          value="" />
	<variable name="trg_projname"
	          value="" />
	<variable name="trg_jobid"
	          value="" />
	<variable name="trg_begtime"
	          value="" />
	<variable name="trg_endtime"
	          value="" />

	<module name="Main">

		<print label="(Status) Error Specs"
		       version="1.0">
			<![CDATA[
================================================================================
** JobLog **
Placing data into ${ LogFile } on ${ Server } Resource
================================================================================
  filename        ${ trg_filename }
  filesize        ${ trg_filesize }
  filetype        ${ trg_filetype }
  lastmod         ${ trg_lastmod }
  trantype        ${ trg_trantype }
  projname        ${ trg_projname }
  jobid           ${ trg_jobid }
  begtime         ${ trg_begtime }
  endtime         ${ trg_endtime }
================================================================================
			]]>
		</print>

		<setVariable label="Init sqlERROR"
		             name="sqlERROR"
		             value="0"
		             version="1.0" />

		<sql label="Connect to Server"
		     resourceId="${Server}"
		     version="1.0"
		     onError="setVariable:sqlERROR=1">
			<query label="Put Log Rec">
				<statement>
Insert into ${ LogFile }
       ( filename, filesize, filetype, lastmod, trantype, projname, jobid, begtime, endtime )
Values ( &apos;${ trg_filename }&apos;,
         ${ trg_filesize } ,
         &apos;${ trg_filetype }&apos;,
         &apos;${ trg_lastmod }&apos;,
         &apos;${ trg_trantype }&apos;,
         &apos;${ trg_projname }&apos;,
         &apos;${ trg_jobid }&apos;,
         &apos;${ trg_begtime }&apos;,
         &apos;${ trg_endtime }&apos; )
				</statement>
			</query>
		</sql>

		<if label="Error Inserting Data?"
		    condition="${ sqlERROR gt 0 }">

			<print label="SQL Insert Error?"
			       version="1.0">
				<![CDATA[
================================================================================
** JobLog Error **
Error Placing data into ${ LogFile } on ${ Server } Resource
================================================================================
				]]>
			</print>

		</if>
	</module>

	<variable name="Server"
	          value="Stats DB"
	          description="Log Server ResourceID" />
	<variable name="LogFile"
	          value="[dbo].filestats"
	          description="Log Table" />
	<description>Write entry to File Stats Database in GoA_Stats DB</description>
</project>

[weesiong]

Thank you, will try :)