February 5, 2009 at 10:54 am
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.
February 5, 2009 at 11:06 am
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
February 5, 2009 at 11:28 am
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.
February 5, 2009 at 11:38 am
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).
February 5, 2009 at 11:46 am
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?
February 5, 2009 at 11:52 am
Here are example values for the FileSystem task:
UseDirectoryIfExists = True
Operation = CreateDirectory
IsSourcePathVariable = True
SourceVariable = User::DirToCreate
For your script dialog:
ReadWriteVariables = FolderExists
February 5, 2009 at 12:34 pm
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
February 5, 2009 at 1:02 pm
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.
February 5, 2009 at 1:14 pm
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.
Thanks Ed for link/help. Looks like i'll have to think of another way to work around this.
June 6, 2013 at 2:40 pm
Hi,
I have the same problem.
Did you found the solution ?
How to prevent the creation of a folder which already exists
June 11, 2013 at 6:57 am
Lidou123 (6/6/2013)
Hi,I have the same problem.
Did you found the solution ?
How to prevent the creation of a folder which already exists
I have not. I just manually rename the first folder created if I ever run into this situation. Fortunately for me it's rare so I just went with the manual solution. Hope you find a solution to your problem and keep us posted.
June 11, 2013 at 11:29 pm
mrtrich99 (6/11/2013)
Lidou123 (6/6/2013)
Hi,I have the same problem.
Did you found the solution ?
How to prevent the creation of a folder which already exists
I have not. I just manually rename the first folder created if I ever run into this situation. Fortunately for me it's rare so I just went with the manual solution. Hope you find a solution to your problem and keep us posted.
I posted some sample code for a Script Task which will create a folder only if it does not exist - see this thread.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 5, 2014 at 12:35 pm
I'm trying to do something similar however, I was trying to use a Script task to set the path and then use Directory.CreateDirectory method as it says it will create it but checks if it exists first. I figured I would create the folder if it doesn't exist, set the archive path and the error paths, do some processing and then if it fails, I uses the error path variable to move the file to the error path via File System Task or if it succeeds, move the file to the archive path. We have issues with folder becoming too full to be able to manually search for a file and the solution is to have the SSIS package divide them up based on a number.
Any suggestions? I'm new to SSIS so any assistance is greatly appreciated.
Thanks,
Barb
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply