September 23, 2008 at 8:50 am
Hi All,
I hope this makes sense, lets say i have folder a and folder b. Within Folder A I have 4 files and in my SQL DB I have a table where the Directory Location and File name is stored. I have an SSIS package to look up the files and move items from folder a to folder b (cut and paste). But every now and then a file is created and the details are not recorded in the DB table. These are then classed as 'orphaned'. Can I get these listed in a table for automated correction/insertion... If i were to have 3 files that are 'orphaned' i want them to appear in a table/list like:
ID LocationDirectory Filename
1 c:/ one.txt
2 c:/ two.txt
I'm not sure if this can be done either in SSIS or SQL as long as I can get detailed in an SQL table.
September 23, 2008 at 9:40 am
I found one method that use's xp_cmdshell but I can't use as we have our db locked down, anyone have and other suggestions?
USE master
GO
CREATE PROCEDURE dbo.sp_ListFiles
@PCWrite varchar(2000),
@DBTable varchar(100)= NULL,
@PCIntra varchar(100)= NULL,
@PCExtra varchar(100)= NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @status int
SET @status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)
SET @Work = 'DIR ' + '"' + @PCWrite + '"'
CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))
INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work
SET @Retain = @@ERROR
IF @status = 0 SET @status = @Retain
IF @status = 0 SET @status = @Return
IF (SELECT COUNT(*) FROM #DBAZ) < 4
BEGIN
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
IF @Wish IS NULL
BEGIN
RAISERROR ('General error [%d]',16,1,@Status)
END
ELSE
BEGIN
RAISERROR (@Wish,16,1)
END
END
ELSE
BEGIN
DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
IF @DBTable IS NULL
BEGIN
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
WHERE 0 = 0
AND (@DBUltra = 0 OR Name LIKE '% %')
AND (@DBUltra != 0 OR Name NOT LIKE '% %')
AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
ORDER BY 1
END
ELSE
BEGIN
SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))
+ ' SELECT SUBSTRING(Name,40,100) AS Files'
+ ' FROM #DBAZ'
+ ' WHERE 0 = 0'
+ CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END
+ CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END
+ CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END
+ CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING
(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END
+ ' ORDER BY 1'
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
END
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
GO
And to test:
EXECUTE sp_ListFiles 'C:\Documents and Settings\All Users\Desktop\FolderName',NULL,NULL,NULL,1
September 24, 2008 at 5:47 am
Hi All, I found a rather useful post/blog that did what I was after. The link is below...
http://agilebi.com/cs/blogs/jwelch/archive/2008/02/02/importing-files-using-ssis.aspx
September 15, 2010 at 2:34 am
Hi,
The statement
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
Does not seem to work for me, but
SELECT ltrim(rtrim(reverse(SUBSTRING(reverse(Name), 1,charindex(char(32),reverse(Name))-1)))) AS Files
FROM #DBAZ
Does the trick
September 15, 2010 at 4:16 am
Try this:
SET NOCOUNT ON
DECLARE @Command VARCHAR(100)
SET @Command = 'dir /b /s '
DECLARE @Folder VARCHAR(100)
SET @Folder = 'D:\AS'
DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @Command
; WITH CTE AS
(
SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder
)
SELECT --FileNames = STUFF ( FileNamesWithFolder , 1 , (LEN(FileNamesWithFolder) - CHARINDEX ('\', REVERSE(FileNamesWithFolder))+1) , '')
FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))
FROM CTE
WHERE ReverseFileNames IS NOT NULL
September 15, 2010 at 4:19 am
:exclamation: ATTENTION Please : Two Year Old Thread
November 16, 2012 at 11:47 am
I've used variations on this in the past, with the insert into #temp exec xp_cmdshell 'dir'
I tried to wrap this in a stored procedure, to be used by other procedures, but then I got the dreaded
[font="Courier New"]An INSERT EXEC statement cannot be nested.[/font]
Suggestions on how to use the results of the procedure in my own temp table?
April 4, 2013 at 9:31 am
The age of a thread does not mean it is not pertinent.
August 6, 2013 at 4:36 am
Just a slight amend to SSCrazy's script and worth a bump as it was very useful. If found that it didn't look in a particular folder as the @folder variable was only defined and never used. It simply required getting rid of that and using the full path in the @Command variable. The updated script is:
SET NOCOUNT ON
DECLARE @Command VARCHAR(1000)
SET @Command = 'dir d:\ftp\clients\somecompany\in /b /s ' -- the full path on the SQL Server instance
DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @Command
; WITH CTE AS
(
SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder
)
SELECT FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))
FROM CTE
WHERE ReverseFileNames IS NOT NULL
September 24, 2013 at 10:37 am
I get this error with the latest revision (containing the full path)
Msg 537, Level 16, State 2, Line 10
Invalid length parameter passed to the LEFT or SUBSTRING function.
Can't figure out why. Also it would be great to pull in the file date, can this be done as a field as well?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
September 25, 2013 at 2:44 am
Well spotted! The SUBSTRING error is occurring because I'm willing to bet that the filepath your using has a space in it somewhere - xp_cmdshell doesn't support spaces (as far as I'm aware) so you have to use the short filename instead. This is easy enough to find by using the "dir /x" command in a DOS prompt. For example, say I have a path of:
c:\ftp\Customer1\DSV Files
The short filename would be:
c:\ftp\Customer1\DSVFIL~1
I'll have to have a play at getting the file date into another column. If you remove the /b parameter (this is the "bare format" parameter) from the dir command you'll get it (along with a lot of other stuff you won't want!), but the CTE will error.
March 24, 2014 at 10:04 pm
I know... old thread. 😉
Just want to correct that last post. xp_CmdShell support names with spaces in them just like DOS does... you have to put double-quotes around the entire path.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2014 at 3:05 am
Cheers Jeff! Glad I qualified that with an "AFAIA" now 🙂
March 25, 2014 at 6:12 am
Heh... no worries. I just love working with xp_CmdShell and wanted to make sure that people knew spaces in a name isn't an obstacle.
Thanks for the feedback..
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply