Migrated files database-view

REAL TIME: Report what files has been processed

The view is named report_migratedfiles and defined on the “Internal Database”.
It contains 1 row for each file that has been processed with or without error.
Notice that you can validate each file in the source or the destination also the applied metadata prior to the result set is retrieved.

Use it with a DataETL Step or a For-Each Step.

From the DataETL Step you can output the reporting result to:
  • Input to other cherry-picking File Management job for
    selective ReMigration or RetryMigration
  • Excel table in a new or existing workbook that subsequently can be uploaded to SharePoint
  • CSV file that subsequently can be uploaded to SharePoint
  • Items in a SharePoint List
  • Records in a database (Internal database, SQL Server or generic ODBC)

Active integrated reporting

The report can be a stand alone job you run or an integrated part of your migration set up since it is based on a standard DataETL Step.
You can choose to create the report at the end of a job or for each iteration in a For-Each and for example only if any errors happen (test the Variable [<ETL-Count>] > 0 to determine if any rows were reported). Then upload an output CSV or XLSX to SharePoint and name it according to the context in which it was created (for example ProjectId or CustomerId etc). Or create Items in a SharePoint list for review. Then finally notify someone or start a workflow using PowerAutomate.

Column name Column description for database view: report_migratedfiles
azurejob_guid SPO: The GUID of the Azure Import API job. Use this GUID to find the exact log feedback from the Azure job that processed the file. Search the Session log for the GUID and look in the report file (AzureJobLog.txt)
copyormove Has the file been copied or moved.
  • Value: Copy
  • Value: Move
createorreplacefile Has the file been created as a new file or has it replaced an existing file.
  • Value: Create
  • Value: Replace
file_destinationguid SPO: The GUID of the file in the Document Library. This value can only be recorded when using the Azure Migration API alias selecting Azure in the mode dropdown list box on the Options tab in the FileManagement Step.
file_destinationname The name the file has on the destination. Note that you can rename files by using the property [<Rename file>] in the FileManagement Step
file_destinationpath The path to where the file has been copied or moved
file_modified The file Modified timestamp.
file_size The file size in bytes
file_sourcename The files name at the source location
file_sourcepath The path to where the source file is located
shortenedpathname Status for if the path or filename was in fact shortened according to the SharePoint/FileShare rules.
  • Value: 0 Not shortened
  • Value: 1 Shortened
hasinvalidmetadata SharePoint: Did the file operation report invalid metadata like badly formatted date/time, non numeric value, non existent SharePoint user name, Managed Metadata tag, Choice value or Content-Type
  • Value: 1 Some metadata was invalid
  • Value: 0 There were no invalid metadata
result The result of the file operation.
  • Value: Ok The operation was successfully completed
  • Value: Pending The file is pending Azure transfer job pickup
  • Value: Warning The file operation was completed with invalid metadata.
  • Value: Error The file operation was NOT completed.
    To query multiple values use the IN operator and specify for example Ok;Warning
result_datetime The date and time for when the file operation attempt was done
result_message The message associated with the file operation attempt. Any specific SharePoint or file system error is also recorded in this column
rowtag custom value that you can assign to each file. Assign your value to the special column [<report_migratedfiles.rowtag>] FileManagement. Use this to connect your own SharePoint list/library id or database id to the record
sessionlog_custominfo The information specified on the Options tab in any Step where you can mix Variables with hard coded text to form a context relevant value for example CustomerId:[<MyCustomerId>].
At runtime the Variable [<SessionLog-CustomInfo>] contains the current value. Use this column in combination with the column [sessionlog_id] and the variable [<SessionLog-Id>] to reference files being processed in the current job for example a For-Each iteration.
sessionlog_id
(Job runtime only)		 The id of the job session in which the file was processed. Use this column in combination with the system Variable [<SessionLog-Id>] to query the files that are being processed by the current job (at runtime). This is useful when doing reporting as part of a job.
Note that you can also use the Variable [<SessionLog-CustomInfo>] to reference files that belong to for example a specific For-Each iteration or other section of the job.
The value for the [<SessionLog-CustomInfo>] Variable is defined on the Options tab in any Step and can contain Variables mixed with hard coded text to form a context relevant value for example CustomerId:[<MyCustomerId>]
sessionlog_name The name you manually type in in the Job tab for a given job session.
sessionlog_row The row number in the session log
sessionlog_test Defines if the file operation was done in test mode i.e. the file has not been attempted copied, just prepared with metadata etc.
  • Value: 1 File not copied. Only simulated using job test-mode.
  • Value: 0 The file was attempted copied.
step_id The Id of the FileManagement Step that performed the file operation. You can see any Step’s id in the properties window that you can open from the Options tab or the Properties toolbar button in the top right of the Step.
step_name The Name of the FileManagement Step that performed the file operation.
Last modified: 18 September 2023