Check if file exists using SQL 2008 R2 script task

Question

Check if file exists using SQL 2008 R2 script task

QQ-485619

Ten Centuries

Points: 1195
More actions
March 17, 2015 at 9:01 pm

#298750

I need to create a script task in sql server 2008 R2 to check if a file exists in a directory. For example, to see if output.dat exist under c:\results. If the file exists, then send out an email stating the file exists, if not then send out another email stating the file does not exists.
I noticed there is a huge difference between the script task in sql 2005 and sql 2008 r2.
Any help would be greatly appreciated.
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

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

CKX SSCrazy Points: 2519 More actions · Answer 1

QQ-485619 (3/17/2015)
I need to create a script task in sql server 2008 R2 to check if a file exists in a directory. For example, to see if output.dat exist under c:\results. If the file exists, then send out an email stating the file exists, if not then send out another email stating the file does not exists.
I noticed there is a huge difference between the script task in sql 2005 and sql 2008 r2.
Any help would be greatly appreciated.
Thanks,

I'm assuming you are speaking of the Script Task within SSIS...

I believe your script task could have code to this effect to test for file existence:

using System.IO;

if (File.Exists(path))

{

// Set some variable you pass in to true

}

You can set Send Email Tasks to run depending on whether the value you have set

is true or false with precedence constraints.

I'm not sure what differences you speak of amongst script tasks that can affect what

you'd like to do but maybe you could elaborate...

QQ-485619 Ten Centuries Points: 1195 More actions · Answer 2

I am not familiar at c#, can you please be more specific?

CKX SSCrazy Points: 2519 More actions · Answer 3

QQ-485619 (3/18/2015)
I am not familiar at c#, can you please be more specific?

Sure.

What I'm suggesting that you could do is add:

1. Add a new variable to your package called "FileExists" setting it's datatype to Boolean

2. Open the properties of your script task and add it as a read/write variable

3. Open up the script task and add the following statement at the top of page in the #region area for Namespaces... using System.IO;

4. Add the code below to the main method of the script task:

if (File.Exists("put your full file path here"))

{

Dts.Variables["User::FileExists"].Value = true;

}

else

{

Dts.Variables["User::FileExists"].Value = false;

}

Dts.TaskResult = (int)ScriptResults.Success;

5. You can add a couple of Send Mail Tasks following the Execution of the Script Task

but in the precedence constraints, set it to expression and test for that FileExists variable you have just set in your script task.

The one could run only if its false and the other if its true.

This is one approach but a simple one if you're just starting out with this kind of stuff.