Flat File Source - File doesn''t exist

Question

Post reply

Flat File Source - File doesn''t exist

Henrique Vieira Pinto

SSC Enthusiast

Points: 102
More actions
February 14, 2006 at 7:26 am

#189237

Hi,

I have a package that reads data from a text file and loads it into a table. The Data Flow works perfectly. The problem is: The package will be run every day, but the text file will not necessarily be there every day. Sometimes the file will exist, and sometimes not. When it doesn't exist, the whole package fails. Is there a way to check if the file exists before executing the data flow task, or is there any other way to solve this problem?? Please, any help would be grateful.

Thanks,
Henrique.

Viewing 6 posts - 1 through 5 (of 5 total)

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Henrique,

I'm not sure whether it works fine or not, but you can try it once.

In the DTS designer, you can place an ActiveX Script as the first step to check for the existence of the flat file. if successful only, you proceed with the other set of tasks in the package.

you can mention this code in the ActiveX Script

Function Main()

Main = DTSTaskExecResult_Success

End Function

Hope this helps you.

Thanks and have a nice day!!!

Lucky

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

There's a nice little task that was created for just this purpose at SQLIS.com (http://www.sqlis.com/default.aspx?23). I've used, not in production yet, and it works very well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Henrique Vieira Pinto SSC Enthusiast Points: 102 More actions · Answer 3

Thanks Grant and Lucky.

Grant, your solution seems to work, but I am not sure if I can install that component on the server I am running the packages..

Lucky, could you be more specific on the script you mentioned? I mean, could you post an example or something like that? I am not so familiar with scripts...

Thanks,

Henrique.

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

Henrique,

Do not use an ActiveX script. They are there for legacy purposes only. If you need to do any scripting then use the Script Task.

Grant's option is the best one. Otherwise you could use a script task to check for the existence of the file. There are classes in .Net that you can use to do this for you - try and .Net forum and you should be able to find what you need.

Or google it: http://www.google.co.uk/search?hl=en&q=.net+check+for+file&meta=

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Henrique Vieira Pinto SSC Enthusiast Points: 102 More actions · Answer 5

Thanks for your suggestion, Jamie!

I've made it! I've used a Script Task to check if the file exists, and it worked pretty well!

If it's useful to anyone, here's the code:

Public

Class ScriptMain

Function FileExist(ByVal sTestFile As String) As Boolean
Dim lSize As Long
On Error Resume Next
lSize = -1
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist =
True

Else

FileExist =

False

End If

End Function

Public Sub Main()

Dim v_filepath As String
Dts.Variables(
"V_INT_File_Exists").Value = 0
If Dts.Variables.Contains("V_STR_File_Path") = True Then
v_filepath =
CType(Dts.Variables("V_STR_File_Path").Value, String)
If FileExist(v_filepath) = True Then
Dts.Variables(
"V_INT_File_Exists").Value = 1

End If

End Sub

End Class