<?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="SalesForce Data Pull"
    emailTo="Timothy J. Bruce &lt;none@example.com&gt;"
>
    <steps>
        <fileImport connectionStringName="SalesForce" updateBatchSize="20" tableName="SF_DataLoad_ErrorLog" namespace="dbo">
            <columnMapping>
                <map from="%wod:FileName%" to="FileName" />
                <map from="Error" to="Error" />
                <map from="Id" to="Id" />
                <map from="CONTACTID" to="ContactId" />
                <map from="EMPLID__C" to="EmplId" />
                <map from="NETID__C" to="NetId" />
            </columnMapping>
            <source xsi:type="delimitedFile" path="\\jgsm-sql01\D$\SF_Downloads\Errors" name="*.Error.txt" fieldSeperator="44" />
        </fileImport>
        <dbCommand connectionStringName="SalesForce" commandType="Text" commandText="delete from dbo.SF_DataLoad_ErrorLog where dbo.TrimToNull( [Error] ) is null" />

        <fileOperation xsi:type="mkDir" path="\\jgsm-sql01\D$\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" />
        <fileExport connectionStringName="SalesForce"
                commandText="select WhenLoaded, [FileName], Count( [Error] ) as ErrorCount from dbo.SF_DataLoad_ErrorLog where ( ( [Error] is not null ) and ( WhenLoaded = Cast( GetDate() as date ) ) ) group by [FileName], [WhenLoaded]"
        >
            <destination xsi:type="delimitedFile" path="\\jgsm-sql01\D$\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorSummary.csv" fieldSeperator="44" writeIfEmpty="false" >
                <columns>
                    <column name="WhenLoaded" formatString="{0:d}" />
                </columns>
            </destination>
        </fileExport>
        <email
            subject="%wod:JobName%: error summary"
            sendIfEmpty="false"
            to="%wod:EmailTo%"
        >
            <attachments>
                <attach path="\\jgsm-sql01\D$\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorSummary.csv" />
            </attachments>
        </email>
        <fileOperation xsi:type="deleteFile" path="\\jgsm-sql01\D$\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="ErrorSummary.csv" />

        <fileOperation xsi:type="mkZip" path="\\jgsm-sql01\D$\SF_Downloads\Errors\Archive\%wod:DateTime{yyyy}%\%wod:DateTime{MM}%" name="%wod:DateTime{yyyy-MM-dd}%T%wod:DateTime{HHmmss}%.zip" truncateEntryName="true" writeIfEmpty="false">
            <sources>
                <source path="\\jgsm-sql01\D$\SF_Downloads" name="debug.log" />
                <source path="\\jgsm-sql01\D$\SF_Downloads\Errors" name="*.Error.txt" />
            </sources>
        </fileOperation>

        <fileOperation xsi:type="deleteFile" path="\\jgsm-sql01\D$\SF_Downloads\Errors" name="*.Error.txt"/>
        <fileOperation xsi:type="deleteFile" path="\\jgsm-sql01\D$\SF_Downloads" name="debug.log" />

    </steps>
</WorkOrder>