We are currently updating our account data extension once everyday using automation studio, I was checking whether we can capture the start time and end time of the activities in automation and the number of records updated so that we can use it for our 3rd party reporting tool. Thank you
You could accomplish in a few different ways to be honest, but here are two that come to mind quickly.
1) Assuming you just want times/counts for the entire automation, you could make a request to retrieve an instance of the desired automation to retrieve start and complete time. Also, you could include a SQL query step in your automation to push all updated records to a DE which could then be fetched with the SOAP API as a metric for all updated users for that daily automation.
2) You could include a script activity at the beginning and end of the automation to insert the start and endtime for a given date to a DE. You could also include a script activity after each step (or as the activity that logs updated records if that's easier) to obtain altered record counts for that automation's run.
I would like to add that though it does not completely fulfill your requirement, you could actually add GETDATE() as a column in your SQL query. For example, you could do:
SELECT column1, column2, GETDATE() AS column3 FROM sourceDE
This will show you the moment each line got copied into the source data extension.
I am not aware of ways to see the number of records that were updated. Do you want to measure the number of records that are selected in your SELECT statement, or the number of records that has changed in the target data extension?
The problem is the soap call returns as starttime and completed time as invalid columns and doesn't show anything for the programID.
<Body> <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI"> <RetrieveRequest> <ObjectType>Automation</ObjectType> <Properties>ProgramID</Properties> <Properties>Name</Properties> <Properties>StartTime</Properties> <Properties>CompletedTime</Properties> <Filter xsi:type="SimpleFilterPart"> <Property>Name</Property> <SimpleOperator>equals</SimpleOperator> <Value>[Test Run]</Value> </Filter> </RetrieveRequest> </RetrieveRequestMsg>