We are getting the files and we are loading the file data in the database table also we need to load the file name as the document name.
For example
We have a table in that we are loading the document data and document name. For the
document name we are extracting the file name from the full path.
Below is the
table.
|
CREATE TABLE file_details( fileid int IDENTITY(1,1) NOT NULL, file_nm varchar(500) NULL, file_data varbinary(max) NULL ) |
Below is the source folder
Below is the
script to extract the file name from the full path and loading that data into
the table.
First of all
we need to set the server property to load file data.
|
USE MASTER GO EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO -- Add 'bulkadmin' to the correct user ALTER SERVER ROLE [bulkadmin] ADD MEMBER [NT AUTHORITY\SYSTEM] GO |
Now we are writing the below code
|
DECLARE @file_nm VARCHAR(250), @Sql_Query NVARCHAR(4000), @Source_Path VARCHAR(500) ='G:\Books\SSIS', @file_path VARCHAR(500), @cmdstr NVARCHAR(400), @n INT=1, @cnt INT, @FileName
NVARCHAR(MAX), @ReversedPath
NVARCHAR(MAX), @ExtLength INT DECLARE @files TABLE ( id INT IDENTITY, file_nm VARCHAR(500) ) SET @cmdstr = 'dir ' + @Source_Path + ' /b'; INSERT INTO @files EXECUTE Xp_cmdshell @cmdstr SET @cnt=(SELECT Count(*) FROM @files WHERE file_nm IS NOT NULL) SELECT @cnt WHILE @n <= @cnt BEGIN SET @file_nm =(SELECT file_nm FROM @files WHERE id = @n) SET @file_path= @Source_Path + '\' + @file_nm; SET @ReversedPath = REVERSE(@file_path) SET @ExtLength = CHARINDEX('.', @ReversedPath) SET @FileName = RIGHT(@file_path, CHARINDEX('\', @ReversedPath)-1) SET @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength) SET @Sql_Query = 'INSERT INTO File_details(File_Nm,File_data) ' + 'SELECT ' + '''' + @FileName + '''' + ', * ' + '
FROM Openrowset( Bulk ' + '''' + @file_path + '''' + ', Single_Blob) as book' EXEC (@Sql_Query) SET @n=@n + 1; END |
Before running this script records in the table.
Now running this script.
See the records in the table.
Data loaded successfully into the table.
