IF Exist Delete file and copy a Template File VB.NET Script Task

Question

IF Exist Delete file and copy a Template File VB.NET Script Task

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 20, 2016 at 7:45 am

#338773

I'm trying to remember how I would check for the existence of a file and Delete it in a VB Script Task.
If it exist I want to Delete the file and copy the template file to the directory where I deleted the file.
I'm search for good articles on how to do this in VB.NET.
Does anyone have a good example?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 9 (of 9 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Please disregard this post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 2

Welsh Corgi (12/20/2016)
Please disregard this post.

If you found an answer to your own question, it would be really cool if you posted your solution because it may help others in the future.

--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)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Jeff,

I only got the delete part complete:

Public Sub Main()

Dim FileToDelete As String

FileToDelete = "U:\ZZZ_ImportCreditDataNew\ImportCreditDataNew\SSISErrorLog.xlsx"

If System.IO.File.Exists(FileToDelete) = True Then

System.IO.File.Delete(FileToDelete)

MessageBox.Show("File Deleted")

End If

Dts.TaskResult = ScriptResults.Success

End Sub

I did not get the copy task.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 4

Welsh Corgi (12/20/2016)
Jeff,
I only got the delete part complete:
Public Sub Main()
Dim FileToDelete As String
FileToDelete = "U:\ZZZ_ImportCreditDataNew\ImportCreditDataNew\SSISErrorLog.xlsx"
If System.IO.File.Exists(FileToDelete) = True Then
System.IO.File.Delete(FileToDelete)
MessageBox.Show("File Deleted")
End If
Dts.TaskResult = ScriptResults.Success
End Sub
I did not get the copy task.
Thank you.

I don't know much about SSIS but I don't believe that VB message boxes will work from an SSIS package. After not working with VB since 2002, I don't remember how to use it copy a file. Why not use a command task?

--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)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Command Task?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 6

Welsh Corgi (12/20/2016)
Command Task?

No... sorry. I meant EXEC Task but, like it says on one of the other two posts on this subject, a File System Task is probably even better.

This is also proof that you shouldn't open multiple posts on the same subject.

--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)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I agree.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323527 More actions · Answer 8

here's a C# code example, change directory to file, and

try

{

string FileName = (string)Dts.Variables["BHCS_InPatient_File"].Value;

if (!System.IO.File.Exists(FileName + ".processed"))

{

System.IO.File.Move(FileName, FileName + ".processed");

}

else

{

System.IO.File.Delete(FileName);

}

catch (Exception ex)

{

Dts.Events.FireError(-1, "Main", ex.Message, "", 0);

Dts.TaskResult = (int)ScriptResults.Failure;

}

VB code via an online converter @ http://converter.telerik.com/:

Try

Dim FileName As String = DirectCast(Dts.Variables("BHCS_InPatient_File").Value, String)

If Not System.IO.File.Exists(FileName & Convert.ToString(".processed")) Then

System.IO.File.Move(FileName, FileName & Convert.ToString(".processed"))

Else

System.IO.File.Delete(FileName)

End If

Catch ex As Exception

Dts.Events.FireError(-1, "Main", ex.Message, "", 0)

Dts.TaskResult = CInt(ScriptResults.Failure)

End Try

'=======================================================

'Service provided by Telerik (www.telerik.com)

'Conversion powered by NRefactory.

'Twitter: @telerik

'Facebook: facebook.com/telerik

'=======================================================

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

IF Exist Delete file and copy a Template File VB.NET Script Task

Cookies on SQLServerCentral