Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using Script Task to check if Folder Exists Expand / Collapse
Author
Message
Posted Thursday, February 5, 2009 10:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, 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.
Post #651093
Posted Thursday, February 5, 2009 11:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #651101
Posted Thursday, February 5, 2009 11:28 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 486, Visits: 1,220
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.



Post #651127
Posted Thursday, February 5, 2009 11:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, 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).
Post #651135
Posted Thursday, February 5, 2009 11:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, 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?
Post #651145
Posted Thursday, February 5, 2009 11:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 486, Visits: 1,220
Here are example values for the FileSystem task:
UseDirectoryIfExists = True
Operation = CreateDirectory
IsSourcePathVariable = True
SourceVariable = User::DirToCreate



For your script dialog:
ReadWriteVariables = FolderExists



Post #651149
Posted Thursday, February 5, 2009 12:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, Visits: 161
Ed (2/5/2009)
Here are example values for the FileSystem task:
UseDirectoryIfExists = True
Operation = CreateDirectory
IsSourcePathVariable = True
SourceVariable = User::DirToCreate



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
Post #651183
Posted Thursday, February 5, 2009 1:02 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 486, Visits: 1,220
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





Post #651203
Posted Thursday, February 5, 2009 1:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, 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.
Post #651216
Posted Thursday, June 6, 2013 2:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 69, Visits: 289
Hi,

I have the same problem.
Did you found the solution ?
How to prevent the creation of a folder which already exists
Post #1460883
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse