How to migrate attachments stored on a fileshare, referenced in MS Access, to SQL Server-CodePudding

I have an MS Access database that we're converting to a SQL Server backend. This database has an Attachments table with a few simple columns:

PK, FK to MainTable.RecordID, Description, filename

Attachments are stored in a fileshare. VBA code uses a hardcoded filepath and ShellExecute to save attachments to a directory, under a RecordID subfolder.

We're moving to store attachments in SQL Server using filestream.

I need to move these attachments from fileshare, to SQL Server, while maintaining RecordID integrity. SQL Server tables and columns are already set up.

These attachments vary in extensions (.msg, .doc, .xlsx, .pdf)

I've been looking into "OPENROWSET" but every example I've seen uses only one file.

I've been looking into SSMA but can't find what I'm looking for.

Any references/reference articles or code resources I can use/repurpose would be greatly appreciated.

CodePudding user response：

Sounds like you want to write an SQL stored procedure that will find all files in a given file path, iterate over those files, and insert the file into a table.

This article will help in general: https://www.mssqltips.com/sqlservertip/5432/stored-procedure-to-import-files-into-a-sql-server-filestream-enabled-table/

This article is about xp_dirtree: https://www.sqlservercentral.com/blogs/how-to-use-xp_dirtree-to-list-all-files-in-a-folder

Here's sample code to read the file system from SQL. THIS IS UNTESTED CODE, you'll need to modify to your needs but it gives you some idea of how to do the loops and read in files.

--You will need xm_cmdshell enabled on SQL server if not already.
USE master
GO
EXEC sp_configure 'show advanced option',1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE WITH OVERRIDE
GO

--Create a variable to hold the pickup folder.
DECLARE @PickupDirectory nvarchar(512) = '\\folder_containing_files_or_folders\';

--Create a temp table to hold the files found in the pickup directory.
PRINT 'Parsing directory to identify most recent file.';
DECLARE @DirTree TABLE (
    id int IDENTITY(1,1)
    , subdirectory nvarchar(512)
    , depth int
    , isfile bit
);

--Enumerate the pickup directory.
INSERT @DirTree
EXEC master.sys.xp_dirtree @PickupDirectory,1,1 --Second variable is depth.

--Create variables to loop through folders and files.
DECLARE @folderCount int;
DECLARE @folderName nvarchar(max);
DECLARE @folderPath nvarchar(max);
DECLARE @i int = 0;

DECLARE @fileCount int;
DECLARE @fileName NVARCHAR(max);
DECLARE @filePath varchar(max);
DECLARE @j int = 0;


DECLARE @RecordID nvarchar(50);
DECLARE @SQLText NVARCHAR(max);
 
SET @folderCount = (SELECT Count(*) FROM @DirTree WHERE isfile = 0);
 
WHILE ( @i < @folderCount )
BEGIN
    --Get the next folder to process.
    SET @folderName = (
        SELECT TOP 1 subdirectory 
        FROM @DirTree as dt
            LEFT OUTER JOIN @processedFolders as pf
                on pf.folder_name = dt.subdirectory
        WHERE isfile = 0 
            AND pf.folder_name IS NULL
    );

    --Get the recordID from folder name.
    SET @recordID = @folderName; --Edit this to get the RecordID from your folder structure.

    --Concat root path and new folder to get files from.
    SET @folderPath = @PickupDirectory   @folderName   '\';

    --Enumerate the this subdirectory to process files from.
    INSERT @filesToProcess
    EXEC master.sys.xp_dirtree @folderPath,1,1

    --Get count of files to loop through.
    SET @fileCount = (SELECT COUNT(*) FROM @filesToProcess WHERE isfile = 1);

    WHILE (@j < @fileCount)
    BEGIN
        --Get next filename.
        SET @fileName = (SELECT TOP 1 subdirectory FROM @filesToProcess WHERE isfile = 1);

        --Concat the whole file path.
        SET @filePath = @folderPath   @fileName;
 
        SET @SQLText = '
            INSERT INTO [table_name](RecordID,[filename],[filestreamCol])   
            SELECT 
                '''   @RecordID   '''
                , '''   @fileName   '''
                , BulkColumn 
            FROM OPENROWSET(Bulk '''   @filePath   ''', Single_Blob) as tb'
 
        EXEC Sp_executesql @SQLText

        DELETE FROM @filesToProcess
        WHERE subdirectory = @fileName;

        SET @j = @j   1;
    END

    INSERT INTO @processedFolders (folder_name)
    SELECT @folderName;

    PRINT 'Folder complete: '   @folderName;

    SET @i = @i   1
END

I think you want to parse just a root directory with the xp_dirtree command above. That will display all the subdirectories which should contain the "RecordID". Read the RecordID into a variable, then parse each of those subdirectories to get the actual files. If you want more detailed code, you'll have to show some examples of the directory structure and the destination table.