I m using sql server 2008 and I need a way to import all the files in a directory. The file names will change but the file layout will remain the same. I have written a Bulk Insert that will add the files once i have the file name. The only thing I am missing is a way to get the file names and possibly add them to a table where I can then run my script against them and import each one.
I cannot use xp_cmdshell and this will be on a occur on a daily bases so I want to automate it if possible.
Thanks, for the reply. I did find another way to do it. I use xp_dirtree
--Create a temporary table to hold the entire list of files in the folder
CREATE TABLE #DirectoryTree ( id int IDENTITY(1,1) ,subdirectory nvarchar(512) ,depth int ,isfile bit);
--Insert all the files in the directory into my temporary table
INSERT #DirectoryTree (subdirectory,depth,isfile) EXEC master.sys.xp_dirtree 'C:\SomeFolder',1,1;
INSERT INTO ImportFiles (subdirectory,depth,isfile,isimported) SELECT subdirectory,depth,isfile,0 FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,4) = '.TXT' AND subdirectory NOT IN(SELECT subdirectory FROM ImportFiles) ORDER BY id;
answered May 09, 2013 at 02:57 PM
create a package in ssis to import
answered May 08, 2013 at 09:12 AM
I agree with @Ramesh1 in that using SSIS is probably the better course of action. Here's an article that might help you get started down this path:
It's older but the logic still holds true.
Hope this helps!
answered May 08, 2013 at 12:45 PM