Issue

Some properties needs to be renamed before the package gets imported into Vault. 


Solution

In order to manipulate a BCP package, bcpToolkit 19 can be used to convert the BCP package into a database that can easily be manipulated.

In following article a BCP package that contains user defined properties is required.


Convert the BCP package to a database

In order to convert a BCP package to database the cmdlet Open-BCPPackage from the bcpToolkit Module can be used:

Import-Module bcpToolkit

#opening the BCP package will result in a database that can be manipulater later
$openPackageResult = Open-BcpPackage -Path 'C:\TEMP\BcpPackage' -Version 2019 -IgnoreBomBlobs


Connect to the database

You can eighter download the required Sqlite assemblies to a desired location, or simply make use of those delivered with bcpToolkit.

You just need to create a SQLite connection object and give it a connection string containing the path to the database. Finally open the connection.

#Add the SQLite dll delivered with bcpToolkit that is located in this directory
Add-Type -Path "C:\Program Files\coolOrange\bcpToolkit\System.Data.SQLite.dll"

#Create a SQLiteConnection Object
$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection

#Specify the path to the database file in the connection string
$con.ConnectionString = "Data Source=$($openPackageResult.DatabaseLocation)"

#Open the connection
$con.Open()


Rename a property in the database

Create a command with a SQL UPDATE statement and execute it. In this example the user defined property (UDP) Designer will be renamed to Engineer.

#Rename UserDefinedProperty 'Designer' to 'Engineer'
$sql = $con.CreateCommand()

$sql.CommandText = "UPDATE UDP SET Name = 'Engineer' WHERE Name = 'Designer';"

$sql.ExecuteNonQuery();


Note: Usually the according PropertyDefinition has to be renamed as well. In our case the PropertyDefinition for Engineer already exists.


Check if the property is updated

Similar to the command for updating a property, we can create a command with a SQL SELECT statement and execute it for getting the renamed property in a dataset.

$sql = $con.CreateCommand()
$sql.CommandText = "SELECT * FROM UDP WHERE Name = 'Engineer'"

#Execute the SQL statement and return the results in  a dataset
$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
$data = New-Object System.Data.DataSet
$adapter.Fill($data)

$data.Tables[0].Rows | Format-Table


Convert the database back to a BCP package

In order to convert the manipulated database back to a valid BCP package we use the cmdlet Export-BCPPackage.

Export-BcpPackage -To 'C:\TEMP\BcpPackage_Modified'

Close-BcpPackage


The package can now be imported into Vault, as described here.


See Also

bcpToolkit (coolOrange wiki)