This article was inspired again by a newsgroup post asking how a directory of files could be read and the filenames be entered into a SQL Server table along with the file itself.
In this article we shall be making use of code we have used previously in other articles along with the Read File transformation type (NOTE: this is not available in SQL Server 7).
The steps that are listed below in the graphic will be explained as we go through the article.
To start with then we are going to need to create two tables
This holds the names of the files in the directory.
CREATE TABLE SourceFileLocations(FileLocation varchar(2000))
This is where we will put the data and file name.
CREATE TABLE LoadedTable(ActualFileName varchar(2000), FileDetails text)