<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
Using Script Task to check if Folder Exists
Using Script Task to check if Folder Exists
Rate Topic
Display Mode
Topic Options
Author
Message
mrtrich99
mrtrich99
Posted Thursday, February 05, 2009 10:54 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:20 PM
Points: 49,
Visits: 157
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
GSquared
GSquared
Posted Thursday, February 05, 2009 11:06 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442,
Visits: 9,571
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
Ed Zann
Ed Zann
Posted Thursday, February 05, 2009 11:28 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 470,
Visits: 1,054
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
mrtrich99
mrtrich99
Posted Thursday, February 05, 2009 11:38 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:20 PM
Points: 49,
Visits: 157
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
mrtrich99
mrtrich99
Posted Thursday, February 05, 2009 11:46 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:20 PM
Points: 49,
Visits: 157
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
Ed Zann
Ed Zann
Posted Thursday, February 05, 2009 11:52 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 470,
Visits: 1,054
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
mrtrich99
mrtrich99
Posted Thursday, February 05, 2009 12:34 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:20 PM
Points: 49,
Visits: 157
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
Ed Zann
Ed Zann
Posted Thursday, February 05, 2009 1:02 PM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 470,
Visits: 1,054
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
mrtrich99
mrtrich99
Posted Thursday, February 05, 2009 1:14 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:20 PM
Points: 49,
Visits: 157
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
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
