| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-10-10 : 03:37:15
|
| Hi,In ssis, I am using a file system task to move a .csv file to a particular folder and Rename it also.here is what I have so far... 1- Variables are: ArchiveDirectoryPath --> \\servername\d$\cfolder\yFolder\Archive SourceDirectoryPath --> \\servername\d$\cfolder\yFolder OutputFileName --> this has an expression as follows: @[User::ArchiveDirectoryPath] + "\\" + REPLACE(@[User::FileName] ,".csv","_")+ "-" + right("0" + (dt_str,4,1252) datepart("d",getdate()),2) + right("0" + (dt_str,4,1252) datepart("m",getdate()),2) + right("0" + (dt_str,4,1252) datepart("yyyy",getdate()),4) + ".csv" I have left a default value of test in the variable window for ArchiveDirectoryPath SourceFilePath --> has an expression as follows: @[User::SourceDirectoryPath] + "\\" + @[User::FileName] I have left a default value of test in the variable window for SourceDirectoryPath . Foreachloop, takes the name of the fully qualified name and assigns it to FileName variable In the FileSystemTask: Is DestinationPath --> true DestinationVariable --> User::OutputFileName Operation --> Move file IsSourcePathVariable --> true SourceVariable --> User::SourceFilePath Problem is that the ssis fails on System Task File and it says: The process can not access the file because it is being used by another process.As far as I can see, the file is not being used anywhere Any thoughts please? Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-10 : 04:32:49
|
| is the file kept open? Also check if variables are getting required values inside loop using local window------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-10-10 : 13:03:11
|
| not as far as I know. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-10 : 14:38:30
|
| check if variables are getting values. Also are there any other parallel tasks which is trying to access the files? will the package ever be executed concurrently in more than one process?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-10-11 : 11:58:22
|
| Hi, In the move file task, I get the following error: Could not find file pathname\test Note that test is the defaultname I have given to SourceFilePath variable inside the variable window. All the other variables and settings are as same as you suggested. I have to have a test default name for @SourceFilePath because if I do not it gives an error which is:SourceFilePath is used as a source or destination and is empty. Any thoughts? Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-11 : 13:15:21
|
| does that path exist? also if not, is it not getting a valid path while inside loop. better to point it to an existing path in local machine and then at runtime it will get correct value from loop.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-10-12 : 03:33:34
|
| I have made some changes and here is my finding:Hi, The error I get in the rename file system task is: Could not find file '[File System Task] Error: An error occurred with the following error message: "Could not find file '\\servername\d$\cfolder\Yfolder\Archive\servername\d$\cfolder\Yfolder\filename.csv'.". This is what I have now: in the rename file system task: Source connection, is sourcepathvariable is set to true and the sourcevariable is set to User::RenameSourceFilePath This variable in the variables windows is set to expression as: @[User::ArchiveDirectoryPath] + "\\" + @[User::FileName] and the EvaluateasExpression is set to true In the value field of the variable window it returns : \\servername\d$\cfolder\Yfolder\Archive In the Destination connection of the file system task for the rename , the IsDestinationiPathVariable is set to true and the DestinationVariable is set to : User::OutputFileName In the variable winidow the OutputFilename variable is set to EvaluateAsExpression to true and Expression is set to @[User::ArchiveDirectoryPath] + "\\" + REPLACE(@[User::FileName] ,".csv","_")+ "-" + right("0" + (dt_str,4,1252) datepart("d",getdate()),2) + right("0" + (dt_str,4,1252) datepart("m",getdate()),2) + right("0" + (dt_str,4,1252) datepart("yyyy",getdate()),4) + ".csv" And so the value field in the variable window shows as: \\servername\d$\cfolder\Yfolder\Archive\-12102011.csv What have I done wrong please? Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 05:08:24
|
path doesnt seem to have correct value\\servername\d$\cfolder\Yfolder\Archive\servername\d$\cfolder\Yfolder\filename.csv' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
move and rename file