#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Archive a Work Area

In this post, we will be “Archiving” a Work Area from a ProjectWise datasource to a SQLite database. The end result of this process is a SQLite database containing nearly all of the data related to the Work Area being archived. This includes the Work Area definition(s), Environment definition(s), folders, documents, document audit trail, and more. This is a near complete snapshot of the Work Area at the time of archiving. This does not include the access control applied to the Work Area. However, this information can be exported to an Excel file and stored with the SQLite database. The physical files are converted to blobs (binary large objects) and inserted into the SQLite database which eliminates dealing with many individual folder and files.

Keep in mind, there are other options to migrate your data. See another post for copying folders from one datasource to another. (Link) This may be a better solution for your specific individual needs.

NOTE: This does require a 32-bit session of PowerShell.  Also, the account used for this process must be able to access all data in the Work Area. It is recommended to turn off access control to eliminate any potential missed data, and to also speed up the process.

We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.12.8.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

  • Get-PWFolders
  • Get-PWDocumentsBySearch
  • Export-PWDocumentsToArchive
  • Export-PWProjectAccessControl

Create and Populate Variables

Here we will create and populate the required variables. Each variable corresponds to a parameter from the Export-PWDocumentsToArchive cmdlet. Except for the ExcelFileName. This variable will be used to export the Access Control data to an Excel workbook.

#region CREATE AND POPULATE VARIABLES

<# This ProjectWiseFolder variable specifies the path to the
ProjectWise Work Area to be exported. #>
$ProjectWiseFolder = 'Projects\BSI900 - Adelaide Tower'
<# The OutputFolder variable specifies the local folder
to be used during the exporting process. #>
$OutputFolder = 'd:\ArchivedProjects'
# The OutputFileName variable specifies the name of the SQLite database to be created.
$OutputFileName = "BSI900 - Adelaide Tower_$(Get-Date -Format yyyyMMdd).sqlite"
<# The ExportFileName variable specifies the file name of the Excel
workbook to be created. This is for exporting the access control. #>
$ExportFileName = "BSI900 - Adelaide Tower_$(Get-Date -Format yyyyMMdd).xlsx"
<# The following two switch parameters are optional.
Include ONLY if you want to EXCLUDE audit trail data. #>
$NoAuditTrail = $false
$ExcludeSecondaryAuditTrail = $false

#endregion CREATE AND POPULATE VARIABLES

Get Folder and Document Counts

Before I archive the Work Area, I like to gather some information pertaining to the Work Area. Here, I will only get the folder and document counts for comparison after the export.  However, it is a good idea to generate some reports containing additional Work Area information such as Access Control (which will will be doing), reference information for MicroStation dgn files.  I will demonstrate a couple of ways to get the object counts.

#region GET FOLDER AND DOCUMENT COUNTS

# Get Folder counts for Work Area to be archived using the Measure-Object cmdlet.
$FolderCount = Get-PWFolders -FolderPath $ProjectWiseFolder |
Measure-Object | Select-Object -ExpandProperty Count
# Get document counts by using the .Count property of the returned objects.
$DocumentCount = (Get-PWDocumentsBySearch -FolderPath $ProjectWiseFolder).Count

# I like Green!
Write-Host "Counts" -ForegroundColor Green
Write-Host " - Folder : $FolderCount" -ForegroundColor Green
Write-Host " - Document : $DocumentCount" -ForegroundColor Green

#endregion GET FOLDER AND DOCUMENT COUNTS

Export To Archive

Now we will export the Work Area to a SQLite database archive. This could be run as a single line in your console without all of the other steps I’ve outlined.  However, if you are scripting and validating, etc., the more information you have the better off you are. My opinion of course.

Here we will use a Splat to define the Parameter / Value pairs using the variables we previously created and populated.

#region EXPORT TO ARCHIVE

$Splat_Export = @{
ProjectWiseFolder = $ProjectWiseFolder
OutputFolder = $OutputFolder
OutputFileName = $OutputFileName
NoAuditTrail = $NoAuditTrail
ExcludeSecondaryAuditTrail = $ExcludeSecondaryAuditTrail
}
Export-PWDocumentsToArchive @Splat_Export -Verbose

#endregion EXPORT TO ARCHIVE

The following shows the splat prior to exporting the Work Area.

splat_Pre

 

Export Access Control

The final step in the process is to export the Access Control associated with the Work Area. There are a couple of different ways to export this data, however, we will be using the Export-PWProjectAccessControl cmdlet. Again, we will be using Splatting to define the Parameter / Value pairs.

#region EXPORT ACCESS CONTROL

$Splat_Access = @{
FolderPath = $ProjectWiseFolder
ExportFilePathName = "$OutputFolder\$ExportFileName"
}
Export-PWProjectAccessControl @Splat_Access -Verbose

#endregion EXPORT ACCESS CONTROL

The following shows the local folder after the exporting process has completed. We end up with two files, one contains the archived data, the other contains the access control data.

windows_Post

SQLite Database

The generated SQLite database contains loads of information. The number of tables returned will vary based on the Work Area / Folder(s) being exported and the options selected.

NOTE: I use SQLiteStudio (3.1.1) to open the sqlite database file.

The following shows the contents of the SQLite database.

sqlite

General_AuditTrail

The General_AuditTrail table contains all of the document audit trail records based on the options selected when exporting.

sqlite_audit


General_Documents_Table

The General_Documents_Table table contains all of the general metadata associated with each document exported to the archive. Such as the document name, create date, created by, etc.

sqlite_docs
General_EnvironmentAttributes

The General_EnvironmentAttributes table contains the document attributes metadata for each document exported.

sqlite_envattrs

General_Environments

The General_Environments table contains a list of the included ProjectWise Environments. These are the environments applied to the folders within the Work Area.

sqlite_env

General_Folders_Table

The General_Folders_Table table contains all of the general metadata associated with each folder exported to the archive. Such as the name, environment, storage, etc.

sqlite_folders

General_ProjectProperties

The General_ProjectProperties table contains the Work Area properties associated with all Work Areas exported to the archive.

sqlite_projprops

General_ProjectTypes

The General_ProjectTypes table contains a list of all Work Area types associated with the Work Areas exported.

sqlite_projtypes

General_Resource_ZipFiles / General_Resources

The General_Resource_ZipFiles / General_Resources tables contain information pertaining to any resources associated with a Work Area which was exported. The General_Resources table contains a list of the resources, while the General_Resources_ZipFiles table contains the corresponding zipfile stored as a blob.

sqlite_resources

General_RichProjectProperties

The General_RichProjectProperties contains all Work Area properties for any Work Areas exported with a Work Area type associated.

sqlite_richprojectproperties

General_RichProjects

The General_RichProjects table lists all of the Work Areas exported.

sqlite_richprojects

General_Users

The General_Users table contains user information for all users related to the work area exported.

sqlite_users

General_Workflows

The General_Workflows table contains any workflow and states which were used within the Work Area exported.

sqlite_workflows

General_ZipFiles

The General_ZipFiles table is probably the most important table. This one contains all of the documents which have been placed into zip files to compress them, and then stored as blobs. Each of the OriginalDocumentGuids correspond to a document within the General_Documents_Table.

sqlite_zipfiles

Access Control

The following shows the output for the Export-PWProjectAccesControl cmdlet.

excel


The following is a link to the full PowerShell script. 

HowTo-ArchiveWorkArea

Experiment with it and have fun. Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.