How to import file information from a file system into a sql server table

You can create a table with the datatype as Image or Varbinary. Then insert the path or file into the table as below.

CREATE TABLE tblData
(
	id INT IDENTITY (1,1),
	FilePath NVARCHAR(MAX),
	FileData VARBINARY(MAX),
	Extn NVARCHAR(20)
)

DECLARE @FilePath NVARCHAR(MAX);
DECLARE @Extn NVARCHAR(20)

SET @FilePath ='C:\Documents and Settings\manik\Desktop\DateFormat.Jpg'
SET @Extn = SUBSTRING(@FilePath,CHARINDEX('.',@FilePath)+1,LEN(@FilePath))

INSERT INTO tblData 
(
	FilePath,
	FileData,
	Extn
)
SELECT @FilePath,@FilePath,@Extn

Select allOpen in new window

If all of my files (jpg, tif, and txt) are located within the same folder, how would I determine which table I should be writing to based upon the file extension? How would I write that into the script?

And I want to be able to loop through all the files in the folder, and do the insert for each one, and then move onto the next file. How would I go about doing that in the script?

You can not do that solely using TSQL. You need the help of a programming language to do this. Anyway Powershell script whould be ideal for you..

This describes how you can iterate through files using powershell

http://technet.microsoft.com/en-us/library/ee176988.aspx

This describes how you can run a sql script from powershell

http://serialseb.blogspot.com/2007/09/executing-sql-scripts-from-powershell.html

If you are new to powershell just google about it. Its awsome scripting language.

Is there a way to loop through the files, and then insert into one of the two tables (depending upon the file type), by using SSIS?

How can I insert the filename from the foreach loop container into my sql server table in an SSIS package? Is there a way to extract other data from the file, such as date, title/name, location, file type, etc?

I am trying to build an Execute SQL task in my dtsx package, with the Expressions Property = SQLStatementSource, and the Expression = INSERT INTO dbo.t_vadoc ([vadoc_batch], [vadoc_docpack], [vadoc_firstimage], [vadoc_lastimage], [vadoc_doctype], [vadoc_firstpage], [vadoc_lastpage], [vadoc_contno])
VALUES (GetDate(), GetDate() , REPLACE( @[User::FileName],'_FIRST', '' ) , REPLACE( @[User::FileName],'_LAST', '' ) , 'GRAPHIC', REPLACE( @[User::FileName],'_FIRST', '' ) , REPLACE( @[User::FileName],'_LAST', ''), GetDate()).

When I try and evaluate the expression, I get the following error message. What am I doing wrong?


ErrorMessage.docx