How to use PowerShell & SQLite to update package file paths

Black and White PDQ logo
Kris Powell|August 23, 2016
Generic blog header
Generic blog header

We’re going to look at using PowerShell and SQLite to update package file paths in PDQ Deploy. Trust me, it’s more exciting than it sounds. During the course of a recent file server migration, lots of scary warnings started popping up all over the place in my Deploy window! What happened? What was I to do?!

PDQ Deploy packages with file path warnings

Our shared file paths were changing and all of my packages suddenly had incorrect file paths.This wouldn’t have been a problem if I had stored my PDQ Deploy packages in the Repository. That’s why it exists. The Repository and the $(Repository) variable allows us to update and reference paths with ease. In fact, that’s one of the biggest reasons that the $(Repository) variable was introduced in PDQ Deploy so very long ago in version 2.

Install Step   Comparison of full file paths and Repository file paths

Which sounds better: updating all your packages one at a time or by updating them all at once by changing a single value, the Repository path?(*psst* As IT admins, we all know the lazy course of action is best. It’s science.)Despite this great time-saving feature in PDQ Deploy, many IT admins (myself included) sometimes fail to include our packages in our Repository.

Sigh. Lesson learned. Store your package files in your repository to avoid this hassle!

For those of us that learned our lesson too late, this blog post is for you. Rather than change all of my hundreds of packages by hand (Pass! Boo! No thanks!), I figured that it would be easier to update them directly in the database. Using PowerShell and SQLite, this is a fairly straightforward task.

Enter PowerShell!

PowerShell and SQLite

Since PDQ products use SQLite for their local database, we have a couple different choices to use with PowerShell and SQLite.

  • Using a PowerShell provider to query the data

  • Piping in SQL (Structured Query Language) directly into SQLite

For simplicity, I’m going to go with piping a SQL query into PowerShell. All we need to do is provide the location of sqlite3.exe (if it’s not in your local PATH), the location of the database file, and the SQL commands that we wish to run. Here’s a quick example of using PowerShell and SQLite to view the Settings table in our PDQ Deploy database:

$db = "C:\programdata\Admin Arsenal\PDQ Deploy\Database.db" $sql = "SELECT * FROM SETTINGS;" $sql | sqlite3.exe $db
PowerShell and SQLite   Selecting all records from Settings table

Update package file paths

Using the information above, we can apply it to the location of the individual file paths in the PDQ Deploy database. All the information about each step is in our database, so we simply need to know tables and values to update within our SQLite database. We’re going to update the path for Install Steps. Let’s break it down into the following steps:

  1. Identify the records to update

  2. Verify our syntax before making any change

  3. Update the records

1 . Identifying the records

Let’s look for any records that start with the file path: \\MyOldFileShare\

$db = "C:\ProgramData\Admin Arsenal\PDQ Deploy\Database.db" $sql = "SELECT FileName FROM InstallSteps WHERE FileName LIKE '\\MyOldFileShare\%';" $sql | sqlite3.exe $db
PowerShell and SQLite   Select FileName from database

2. Verifying the syntax

We’re going to use the REPLACE command to find and replace the value \\MyOldFileShare\ with \\NewFileShare\Deploy\ for our files. This is still a SELECT command, so this will not update the database. We’re going to use this to verify our syntax.

$db = "C:\ProgramData\Admin Arsenal\PDQ Deploy\Database.db" $sql = "SELECT REPLACE(FileName, '\\MyOldFileShare\', '\\NewFileShare\Deploy\') FROM InstallSteps WHERE FileName LIKE '\\MyOldFileShare\%';" $sql | sqlite3.exe $db
 PowerShell and SQLite   Select and Replace FileName from database

3. Update database with changes

Now that we have the correct syntax, we’re going to update the InstallSteps table with the Replace value that we identified in step 2. The UPDATE command doesn’t return any values, so I have added an additional SELECT command to run immediately after.

$db = "C:\ProgramData\Admin Arsenal\PDQ Deploy\Database.db" $sql = "UPDATE InstallSteps Set FileName = REPLACE(FileName, '\\MyOldFileShare\', '\\NewFileShare\Deploy\') WHERE FileName LIKE '\\MyOldFileShare\%';" $sql | sqlite3.exe $db $sql = "SELECT FileName FROM InstallSteps WHERE FileName LIKE '\\NewFileShare\%';" $sql | sqlite3.exe $db
PowerShell and SQLite   Update FileName in Database

Now that we’ve updated our database, we can see those pesky warning triangles disappear from our PDQ Deploy console. I simply switch back to my PDQ Deploy console and tap the F5 button to refresh: 

PDQ Deploy after updating FileName

Yes! No more warnings! PowerShell and SQLite to the rescue! Now we can deploy to our heart’s content once more.

Repository and variables: Paths made easy

First and foremost, if you are able to store all your files in your repository, that is what we recommend the most. If you are unable to store your files in your repository, our next best recommendation is to use a variable (Preferences > Variables) to store your file paths.

Using files paths in variables

Once we do this, we can reference this variable directly within our packages.

Using variables in the file path

Now, any time we need to update our file paths, we will only need to update our variables. Use the SQLite update examples from above and replace \\MyOldFileShare with either the repository or custom path variables.

Final notes

This only will update the file path for Install Steps. If you need help with other step types, please let me know. Hopefully, you will never need to update the file paths in all your packages. Because boo to that. If you do find yourself in a similar situation, however, PowerShell and SQLite can help you out. You should be able to use this blog post to update your install file paths within each of your PDQ Deploy packages. Give it a shot! This has personally saved me a lot of manual effort.

Did you know that PDQ Deploy has a PowerShell step you can use to deploy your scripts?

Black and White PDQ logo
Kris Powell

Kris was an employee at PDQ.

Related articles