SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Script Task to check if Folder Exists


Using Script Task to check if Folder Exists

Author
Message
mrtrich99
mrtrich99
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 161
I am creating a new directory/folder in my package, but before I do that I want to check if the directory/folder has not been created. The way these folders are getting created is by using the date the package was run, so between 1 and 15 of the month, the folder will get a folder name of, for example, 01-15-2009. The reason why I want to check if the folder exists is because I might potentially use the package on, for example, the 2nd and 15th of the month so the package will error with the 15th run time because the directory has already been created on the 2nd. I don't believe there is a rule inside the "Create Directory" of the File System Task so I started to write a Script Task that will check if the Folder Exist. Below is what I currently have but the script errors out because it doesn't like the DTS.variable I'm assigning.

Dim di As DirectoryInfo = New DirectoryInfo("E:\test\01-15-2009")

If di.Exists = True Then
Dts.Variables("User::FolderExists").Value = True
Else
Dts.Variables("User::FolderExists").Value = False
End If

Dts.TaskResult = Dts.Results.Success

User::FolderExists is a variable I created which is boolean with default value of False.

The "E:\test\01-15-2009" folder is hard coded in there for testing purposes because it already exists. Once I figure out why the script doesn't like the User::FolderExists code, I will replace the dated sub directory with a SSIS variable that will determine this folder name.

Anyone know why the script errors out?

Thanks.
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31049 Visits: 9730
There's an option in SSIS file system tasks to not create the object (file or folder) if it already exists. Will that do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Ed Zann
Ed Zann
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1111 Visits: 1391
As G squared indicated, there is a UseDirectoryIfExists property you can set to True in the FileSystem Task. As to why your code isn't working - two things you might look into:

1 - Variable Scope - can the task see it?

2 - Is it designated ReadWrite on the script dialog?

I clipped your code and ran it sucessfully, so there must be something in the setup.



mrtrich99
mrtrich99
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 161
I have UseDirectoryIfExists set to True, but it errors out as it tries to create it. If I set it to False it gives me an "X" saying it "Can not create a directory. User chose to fail the task if directory exists" and the package doesn't run so my only option is setting it to True.

Is this supposed to be the case?

I have the IsSourceVariable set to False and using a SourceConnection Path that pointing to the folder above (well, with the dynamic variable dating of the folder in the path).
mrtrich99
mrtrich99
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 161
Ed (2/5/2009)
As G squared indicated, there is a UseDirectoryIfExists property you can set to True in the FileSystem Task. As to why your code isn't working - two things you might look into:

1 - Variable Scope - can the task see it?

2 - Is it designated ReadWrite on the script dialog?

I clipped your code and ran it sucessfully, so there must be something in the setup.


In regards to #2, how do I designate ReadWrite on the script dialog? Currently it's blank for ReadOnlyVariables and ReadWriteVariables. What should it be for these two?
Ed Zann
Ed Zann
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1111 Visits: 1391
Here are example values for the FileSystem task:
UseDirectoryIfExists = True
Operation = CreateDirectory
IsSourcePathVariable = True
SourceVariable = User:BigGrinirToCreate



For your script dialog:
ReadWriteVariables = FolderExists



mrtrich99
mrtrich99
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 161
Ed (2/5/2009)
Here are example values for the FileSystem task:
UseDirectoryIfExists = True
Operation = CreateDirectory
IsSourcePathVariable = True
SourceVariable = User:BigGrinirToCreate



For your script dialog:
ReadWriteVariables = FolderExists


Thanks Ed for the ReadWriteVariables, the Script Task now executes.

As far as the FileSystem task for Creating the directory, I would like to keep my settings as is because it works with the dynamic variable i'm using to create the folder. Here's what I have

UseDirectoryIfExists = True
Operation = CreateDirectory
IsSourcePathVariable = False
SourceConnectoin = test (this is the test folder + plus the dynamic variable which will be the date, so in the end it will be E:\test\01-15-2009

Can I use something in the Expression to disable this FileSystem if the folder exists? I used this in the Expression of the file system and it error out so I'm not sure if I'm using the correct Property:

Property = Disable
Expression = @[User::FolderExists] ==true
Ed Zann
Ed Zann
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1111 Visits: 1391
Well... I've learned something here. You can't really disable a task at runtime. What you can do is put an expression in the workflow precedence (the arrow) to indicate whether the task following should execute. Also, if there are subsequent tasks, they will all execute regardless of the "failed" status of a preceding workflow.

See http://blogs.conchango.com/jamiethomson/archive/2005/10/05/SSIS_3A00_-Disabled-tasks-don_2700_t-behave-as-you-would-expect_2100_.aspx



mrtrich99
mrtrich99
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 161
Ed (2/5/2009)
Well... I've learned something here. You can't really disable a task at runtime. What you can do is put an expression in the workflow precedence (the arrow) to indicate whether the task following should execute. Also, if there are subsequent tasks, they will all execute regardless of the "failed" status of a preceding workflow.

See http://blogs.conchango.com/jamiethomson/archive/2005/10/05/SSIS_3A00_-Disabled-tasks-don_2700_t-behave-as-you-would-expect_2100_.aspx




Thanks Ed for link/help. Looks like i'll have to think of another way to work around this.
Lidou123
Lidou123
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 380
Hi,

I have the same problem.
Did you found the solution ?
How to prevent the creation of a folder which already exists
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search