May 14, 2009 at 1:49 pm
I have a very similar problem. I dynamically construct source and destination paths which works perfectly on with the copy method. If I change the method to move it raises the error "Could not find part of the path"
As a matter of fact if I copy from the source dir to the destination dir, then delete from the source dir I get what I want...but runs longer of course.
I have not found a remedy yet.
May 15, 2009 at 12:52 am
jcwilliams (5/14/2009)
Ramesh (5/14/2009)
I don't know why it is not working at your end, but it works fine at my machine. I've attached a sample package that I've created.Let us know if it works for you.
Ramesh, with a little tweaking with the code to reflect my environment it worked.
I never thought about doing away with Connection Managers.
Thanks for all your input!!!
You are welcome!!
--Ramesh
May 15, 2009 at 1:07 am
jgenshlea (5/14/2009)
I have a very similar problem. I dynamically construct source and destination paths which works perfectly on with the copy method. If I change the method to move it raises the error "Could not find part of the path"As a matter of fact if I copy from the source dir to the destination dir, then delete from the source dir I get what I want...but runs longer of course.
I have not found a remedy yet.
Change it to "Rename file" method, this should work.
--Ramesh
May 15, 2009 at 9:44 am
Yes, that worked....funny.
I wonder if this is a bug.
May 16, 2009 at 12:53 am
jgenshlea (5/15/2009)
Yes, that worked....funny.I wonder if this is a bug.
That's not a bug, but indeed the intended behaviour of the operations "Move file" & "Copy file". These operations does not allow the destination file name to be changed.
--Ramesh
May 18, 2009 at 10:20 am
In my situation, I am not changing the file name, I need to move a file from a "working" directory to an "archive" directory, and the copy combined with the delete methods work, the rename method works (although I am only changing the destination path), BUTthe move method does not work. I am not a SSIS elite, but if the copy method works with the parameters I supply it then I assert that the move method should also work. Given that the shell interface are essentially the same for a copy command as a move command I don't know why SSIS developers would opt to make it more complex in the SSIS GUI.
December 9, 2009 at 6:01 am
Hello guys, thanks for your examples. I had almost the same problem but I need to copy *.dat files from a folder that change the name every day using the date:
Example:
copy file from C:\folder1\2009-12-09\logs\*.dat
destination folder C:\folder1
The problem is that tomorrow the source folder path will change as following:
C:\folder1\2009-12-10\logs\*.dat
Any Ideas?
Thanks in advance
December 9, 2009 at 7:36 am
jeirix (12/9/2009)
Hello guys, thanks for your examples. I had almost the same problem but I need to copy *.dat files from a folder that change the name every day using the date:Example:
copy file from C:\folder1\2009-12-09\logs\*.dat
destination folder C:\folder1
The problem is that tomorrow the source folder path will change as following:
C:\folder1\2009-12-10\logs\*.dat
Any Ideas?
Thanks in advance
[font="Comic Sans MS"]
For your situation - define a variable - say User::SourcePath of string type and use string functions to get it in the desired format ..
Then use the variable in filesystem/script task to move files..
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 9, 2009 at 12:00 pm
Thanks for the reply.
I have created a variable called SourcePath with a value C:\folder1\logs
then under logs folder I have all date folders (2009-12-08;2009-12-09;...)
So I've created another variable called DynamicPath with the following expression:
@[User::SourcePath]+ (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".dat"
I used -1 because I need the day before today values.
But when I look on the Evaluated value it returns:
C:\folder1\logs\2009-12-08.dat
My target is to reach the .dat files under the following path
C:\folder1\logs\2009-12-08\2009-12-08.dat
Thanks for your suggestions
December 9, 2009 at 2:33 pm
[font="Comic Sans MS"]
The @[User::SourcePath] variable should look like this - "C:\folder1\logs\" an extra slash at end.
Now evaluate the DynamicPath as :
@[User::SourcePath]+ (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) +"\\"
Now you have 2 options :
a) Use a script task (google it - and there are many examples available as I remember)
b) can use a for each loop container and do the rest as follows :
Define a variable @[User::FileJustFound]
Go to collection - select foreachfile enumerator
In expression - set :
Directory as @[User::DynamicPath]
FilenameRetreival : "NameOnly"
Filespec : "*.dat"
In Variable Mappings: Add the variable @[User::FileJustFound] & Set Index = 0. Hit OK.
Now use a filesystem task inside the foreach loop container to do the job ...
Option (a) is easier and efficient
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 10, 2009 at 6:01 am
Hi Sabya thanks for the great reply, I add the back slashes.
The Evaluated values shows the correct path (C:\folder1\logs\2009-12-09\)
Then I create a new variable called MyFileValue (the filename is PL2009-12-09.dat)
using the same procedure as for reach the date folder so the expression starts like:
"PL"+(DT_WSTR,4)....+".dat"
The problem now is that the container returns an errors "The path is empty"
And the file system task returns:"Could not find a part of the path 'C:\folder1\logs\'."
I've note that if a define a new variable say FullSourcePath with @[User::SourcePath]+@[User::DynamicPath]+@[User::MyFileValue] the Evaluated value returns only the SourcePath
which C:\folder1\logs\ instead C:\folder1\logs\2009-12-09\IP2009-12-09.dat
What I doing wrong?
Thanks again!!!
December 10, 2009 at 8:17 am
[font="Comic Sans MS"]
Hi Jeirix,
See http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx for a visual help in case you have missed anything.
Try setting delayvalidation property of the task to be true and execute. If you have configured the expression properly it should work ..
As the variable values are evaluated only during runtime you won't be able to see untill the container/task is executed.
Let me know how it goes..
-------
BTW:- How did you see the variable values (as they are evaluated only during runtime)? This is an extremely helpful video to show how you can see intermediate variable values:
http://www.sqlservercentral.com/articles/Video/64762/
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 14, 2009 at 8:26 am
Hello Sabya,
thanks again!
I check with debug and I saw the value was wrong on the variable. The loop container didn't work correctly.
I've made some test manually renaming the file to make it more easy from IP2009-12-13_00235.dat to IP2009-12-13.dat
And It's working fine my last problem (really my last one :D) is to find a command to recognize the .dat file even the name isn't complete, because the last part refers to the time and it's alway different!
I've figured out by adding more figures on Connection Manager instead :
New file connection --> usage type : existing file
file : C:\folder1\logs\2009-12-13\IP2009-12_000235.dat (even I set a non dynamic path then with the expression it will change)
Then i went into expression under property tab I set "ConnectionString" and under Expression:
@[User::SourcePath] +(DT_WSTR, 4)YEAR(DATEADD("dd", -1, GETDATE() ))+"-"+
RIGHT("0"+(DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE() )),2) +"-"+ RIGHT("0"+(DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE() )), 2 )+"\\"+
"IP"+(DT_WSTR, 4)YEAR(DATEADD("dd", -1, GETDATE() ))+"-"+
RIGHT("0"+(DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE() )),2) +"-"+ RIGHT("0"+(DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE() )), 2)+".dat"
with this expression I could copy the file I need in the correct complete path everyday.
My last step is find out a command to "cut" the last part of the filename ( _000235) which refers to the time.
I hope my almost complete solution is clear, anyway I'm working on it.
Thanks! Regards.
J
December 14, 2009 at 4:05 pm
[font="Comic Sans MS"]
Hello - you are right on track. Use string function .. findstring and substr ...
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 21, 2009 at 9:59 am
Hello Sabya,
I couldn't reply before because I was a bit busy with another job.
Finally i could fix my problem moving file by using dynamic folders and dynamic name file adding a
Foreach Loop Container as attached.
I let your dynamic syntax in the connection manager as show in the red round.
Only need to add a variable to store when Loop Container runs and use the *.dat extension that was all.
Thanks again for your help!
Best Regards.
jeirix
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply