PowerShell Module to Read Directory Contents and Store in a SQL Server Table

By: Nisarg Upadhyay   |   Updated: 2019-01-28   |   Comments (26)   |   Related: > PowerShell

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

About the author

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips