PowerShell to Read Directory Contents and Store in SQL Server Table

Problem

In this tip we will walk through how to create a PowerShell module to get a list of files and subfolders of a Windows folder and store this information in a SQL Server table.

Solution

In this tip, I am going to explain the code of a PowerShell module which gets a list of files that reside in a folder and stores this information in a SQL Server table.

I am going to:

1. Create a SQL table that has a column to store FQN (Fully qualified name) of any file.
2. Show how to write the PowerShell code.
3. Execute PowerShell function using T-SQL.

In this tip, I have used the following PowerShell cmdlets.

Create the SQL Server Table

To store the directory contents in a table, first I will create a table named “tblFileLocations” in database “DemoDatabase“. The following code creates the SQL table.

USE DemoDatabase
GO
CREATE TABLE [dbo].[tblFileLocations](
[ID] [int] IDENTITY(1,1) NOT NULL,
[File_name] [varchar](max) NULL,
       [FileLocation] [varchar](max) NULL,
[CreateDate] varchar(50) NULL,
[LastChangeDate] varchar(50) NULL,
[FileSize] [numeric](10,5) NULL,
PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create a PowerShell Module

As I mentioned, I want to get the list of files within a specific directory. To do that, we will create a PowerShell cmdlet. It accepts the Drive Letter as a parameter, iterates through the entire drive and populates the following details:

1. File Name
2. Fully qualified file name
3. File attribute
4. Last access time
5. Last modified time
6. File Size

To execute this function using a SQL Stored procedure, we must register it as a PowerShell module. Once the PowerShell module is registered, we add this module in the PowerShell profile.

First, let’s understand the PowerShell script.

To understand the PowerShell code, I have split it into multiple sections:

Script Part-1

Create a PowerShell function. This function accepts the Drive Letter as a parameter. The following code block creates the function.

function global:getFileLocations
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
)
}   

Script Part-2

Construct a string variable named “$sqlstatment” that has an “Insert” query statement. The following code creates a string variable.

$sqlstatement=@'
INSERT INTO tblFileLocations
(
File_name,
FileLocation, 
CreateDate,
LastChangeDate,
FileSize    
) 
VALUES (
'{0}',
'{1}',
'{2}',
'{3}',
'{4}'    
)
'@

Script Part-3

Populate list of files command and formats the output according to the insert query stored in the “$sqlstatement” variable. The following is the code block.

Get-ChildItem -Recurse $FileLocation  | 
select Name,FullName,CreationTime,LastWriteTime,
@{Label="Length";Expression={$_.Length / 1KB -as [int] }}  |
ForEach-Object 
{
$SQL = $sqlstatement -f  $_.name,$_.FullName, $_.CreationTime,$_.LastWriteTime,$_.Length
Invoke-sqlcmd -Query $SQL -ServerInstance "TTI412-VM\SQL2017" -database "DemoDatabase"
}

The above code block performs the following tasks:

1. It passes “$FilePath” parameter to “Get-ChildItem -Recurse” cmdlet and populates the list of files. “$FilePath” parameter is drive letter/ directory location (C:\Temp\…).
2. Format the output, generated by “Get-ChildItem -Recurse” in multiple columns. Column sequence of output and the insert statement which is stored in “$sqlstatement” parameter must be the same.

4. It creates a new string variable named “$SQLQuery” by concatenating “$sqlstatement” and the values generated by “Get-ChildItem -Recurse” command. The format of “$SQLQuery” parameter is like a T-SQL insert statement. Using “ForEach-Object” cmdlet, it generates multiple insert statements and stores it in a $SQLQuery variable. See the following image:

5. By using “Invoke-SQLCmd” command, it inserts the value of the $SQLQuery variable into the tblFileLocations table.

Complete PowerShell Script

function global:getFileLocations
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
)
Write-Output "Inserting File Locations in table"
$sqlstatement=@'
INSERT INTO tblFileLocations(
File_name,FileLocation,CreateDate,LastChangeDate,FileSize    
) 
VALUES (
'{0}',
'{1}',
'{2}',
'{3}',
'{4}'    
)
'@
Get-ChildItem -Recurse $FileLocation  | 
select Name,FullName,CreationTime,LastWriteTime,
@{Label="Length";Expression={$_.Length / 1KB -as [int] }}  |
ForEach-Object {
$SQL = $sqlstatement -f  $_.name,$_.FullName, $_.CreationTime,$_.LastWriteTime,$_.Length
        Invoke-sqlcmd -Query $SQL -ServerInstance "TTI412-VM\SQL2017" -database "DemoDatabase"
}
Write-Output "File locations are saved in table"
}

Save Script as a PowerShell Module

To execute the PowerShell function using T-SQL, register this script as PowerShell module. To register any PowerShell script, first a directory must be created in “C:\Windows\System32\WindowsPowerShell\v1.0\Modules”. The name of the PowerShell script and directory must be the same. To register the above script, perform the following tasks:

1. Create a new directory named “getFileLocations” in “C:\Windows\System32\WindowsPowerShell\v1.0\Modules” directory.
2. Save the PowerShell script as getFileLocations.psm1 in “getFileLocations” directory.

Execute the PowerShell Function in T-SQL Script

To execute a PowerShell function in a stored procedure, enable xp_cmdshell. To do that, execute the following command:

use master
go
exec sp_configure 'show advanced option',1
reconfigure with override
exec sp_configure 'xp_cmdshell',1
reconfigure with override

To execute the PowerShell script using a T-SQL query, use the “powershell.exe” command. As I mentioned, our script inserts the fully qualified path of files located in a specific drive or specific directory, hence we must create a parameterized T-SQL script. In the script, we can pass the drive letter OR directory path as a parameter.

For this demonstration, I created a folder named “UPADHYAY ALBUM“” in my E drive. I copied a few SQL scripts to the folder. I am going to insert the file details of all directories and files located in the E:\UPADHYAY ALBUM\ folder. To do that, execute the following code:

declare @PSScript varchar(2500)
declare @FileLocation varchar(2500)
set @FileLocation='D:\UPADHYAY ALBUM'
set @PSScript= 'powershell.exe getFileLocations ''' + @FileLocation +'''' 
exec xp_cmdshell @PSScript

Let’s verify that whether file details are stored in a table using the following code:

SELECT * FROM tblFileLocations

As you can see in the following output, the fully qualified names and details from the “E:\UPADHYAY ALBUM\” directory is stored in the “Fully_Qualified_FileName” ” column.

Summary

In this article, I explained:

1. How to create a PowerShell module and how the PowerShell script works.
2. Execute the PowerShell function using T-SQL code.

Next Steps

• Check out these other PowerShell tips

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server and 2 years with Oracle 10g including: database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University. When Nisarg is away from computers, he loves travelling, reading books and watching Sci-Fi movies.

• MSSQLTips Awards: Rookie of the Year Contender – 2018