finding file existence

Question

finding file existence

ramu4ind

SSC Eights!

Points: 865
More actions
September 16, 2008 at 9:02 am

#198278

My requirement is that i need to find whether the file exist or not in dynamically becasue filename will be change.based on naming convention servername.date.How does it possible?Any suggestion on this highly appreciable
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

use a for each loop using the for each file in folder and a script task inside the loop to examine the file names then set a boolean variable if the file exists.

ramu4ind SSC Eights! Points: 865 More actions · Answer 2

thanks for your fast reply.I want little bit deep.that is what i need to write in script task.

TangoVictor SSChampion Points: 10975 More actions · Answer 3

when the script task is in the loop it will execute for each file name retrieved. Make sure a variable is setup in the for each container to receive the file name.

Enter this variable as a read only on the script task. In my example - "Filename"

also need a read write variable, in my example "IsFile"

Public Sub Main()

'

' your code here

'Get the file name to look for

Dim fileToFind As String

fileToFind = "MyServerName." + Format(Now(), "yyyyMMdd")

If Dts.Variables("Filename").Value.ToString = fileToFind Then

Dts.Variables("IsFile").Value = True

End If

'

Dts.TaskResult = Dts.Results.Success

End Sub

This should get you started.

Good Luck !

ramu4ind SSC Eights! Points: 865 More actions · Answer 4

I tried but i didn't get the expected result means i have files in a folder.Folder path is c:\server\servername.date how to set this path in script task.I tried but not getting exact

Thanks

elegantkvc SSCommitted Points: 1910 More actions · Answer 5

hi,

You have to add another vairable for storing the path and use that path in the design script area..set the variable scope to the package.

you can use the system.io.file.exist("filepath") to return the boolean value....

Regards

vijay

ramu4ind SSC Eights! Points: 865 More actions · Answer 6

I tried this code but not able to get the result correctly .can you correct this code

Dim fileToFind As String

Dim path As String

path = "c:\"

'fileToFind = "c:" & "\\" & "ramu" & "\\" & "ramu." & Format(Now(), "yyyymmdd")

fileToFind = path & "ramu" & "\" & "ramu." & Format(Now(), "yyyymmdd")

Dts.Variables("User::IsFile").Value = Right(fileToFind, Len(fileToFind) - 1).Replace("\", "\\")

'If Dts.Variables("Filename").Value.ToString = fileToFind Then

If System.IO.File.Exists(fileToFind) Then

Dts.Variables("IsFile").Value = True

Else

Dts.Variables("IsFile").Value = False

Dts.TaskResult = Dts.Results.Failure

End If

'

Dts.TaskResult = Dts.Results.Success

elegantkvc SSCommitted Points: 1910 More actions · Answer 7

hi,

Had you got any error or the wrong result......whats the result you got with the above code...

vijay

ramu4ind SSC Eights! Points: 865 More actions · Answer 8

These were coming in progress.Nothing was done in data flow

[Flat File Source [1]] Warning: The system cannot find the path specified.

[Flat File Source [1]] Error: Cannot open the datafile "".

[DTS.Pipeline] Error: component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC020200E.

elegantkvc SSCommitted Points: 1910 More actions · Answer 9

hi,

I think surely its an permission or accessibility related error.Check the following..

When you are getting this error. Do you get this error when it is running from the visual studio .net or from the sql server agent.

Is this file is it in a remote machine. If your file is in remote machine and you have given UNC path, make sure that your sql agent user account has permission to the folder.

Regards

vijay

ramu4ind SSC Eights! Points: 865 More actions · Answer 10

It is local system.While executing the package in visual studio.i am getting the error.

can you tell me how to set the path for c:\ramu\ramu.20080917.txt and somany files in the same format under ramu folder.As explained above the path might be missing.

Thanks

elegantkvc SSCommitted Points: 1910 More actions · Answer 11

hi,

Rename the file to some abc.txt and try it mate...It will work

vijay

Doug Elwood-471888 SSC Eights! Points: 953 More actions · Answer 12

Hi,

Is there any reason that this T-SQL code will not work for you?

declare @Filename varchar(255),

@Servername varchar(255),

@Date varchar(10),

@FilePresent int

-- Variable @FilePresent = 1 if the file is found.

SELECT @Date = convert(varchar(10),getdate(),112), @Servername = 'MyServer'

--Set @Filename = 'C:\mydir\' + @Servername + '.' + @Date

Set @Filename = 'C:\WINDOWS\winhlp32.exe' -- just to show it works

exec master.dbo.xp_fileexist @Filename,@FilePresent OUTPUT

select @FilePresent as '1 if file exists'

I know that xp_fileexist is an undocumented stored procedure and might be discontinued in future versions of SQL Server, but it is there now and seems to address this problem as described.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

It's even easier than that...

SELECT *

INTO #MyFiles

FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.xp_DirTree ''C:\Temp'',1,1')

SELECT * FROM #MyFiles WHERE [File] = 1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)