<?xml version="1.0" encoding="utf-8"?>
<WorkOrder xmlns="http://Icod.Wod" xsi:schemaLocation="http://Icod.Wod \\jgsm-sql01\D$\Icod.Wod\schema0.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
jobName="Consortium Get Recommender Filenames"
emailTo="[email protected]"
>
<steps>
<dbCommand connectionStringName="SalesForce" commandType="Text" commandText="delete dbo.Consortium_PDFUpload_tbl" />
<fileOperation xsi:type="deleteFile"
path="\\jgsm-sql01\D$\Consortium Imports\Archive" name="list"
/>
<fileOperation xsi:type="listFile" searchOption="AllDirectories"
path="\\jgsm-sql01\D$\Consortium Imports\Archive" name="*.pdf" regexPattern="\d{6,8}_((App)|(\d{6,8}_REC))\.pdf$"
>
<destination path="\\jgsm-sql01\D$\Consortium Imports\Archive" name="list" />
</fileOperation>
<fileImport connectionStringName="SalesForce" namespace="dbo" tableName="Consortium_PDFUpload_tbl" updateBatchSize="20" >
<columnMapping>
<map from="FileName" to="FileName" />
</columnMapping>
<source xsi:type="delimitedFile" path="\\jgsm-sql01\D$\Consortium Imports\Archive" name="list" hasHeader="false">
<columns>
<column name="FileName" />
</columns>
</source>
</fileImport>
<dbCommand connectionStringName="SalesForce" commandType="Text" commandText="update dbo.Consortium_PDFUpload_tbl set Type = 'App' where Right( Filename, 8 ) = '_App.pdf'" />
<dbCommand connectionStringName="SalesForce" commandType="Text" commandText="update dbo.Consortium_PDFUpload_tbl set Type = 'REC' where Right( Filename, 8 ) = '_REC.pdf'" />
<dbCommand connectionStringName="SalesForce" commandType="Text" commandText="update dbo.Consortium_PDFUpload_tbl set HobsonsID = Try_Cast( dbo.AllTrim( Replace( Substring( Filename, 1, CharIndex( '_', Filename, 1 ) ), '_', ' ' ) ) as int )" />
</steps>
</WorkOrder>
Comments