File System Task: Move/Rename

I can use the File System Task to Move a file from one directory to another and to rename a file, but is there a way to do this in one step, i.e., move file named 'c:\foo.txt' to 'c:\backup\foo_bkp.txt'?You should be able to accomplish this in an expr.|||

OK, maybe I'm being dense ... but how? I have both Source and Destination set to variables. The "Move File" facility only accepts a directory as the Destination because when I coded it with a new file name, I got an error due to the destination file name being ["DestVariable"]\["SourceFileName"] so it looked like:

c:\backup\foo_bkp.txt\foo.txt

|||Raul,
You can use a script task to do this. See here: http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

-Jamie

File System Task Problem

I'm trying to realize a file system task that rename files from a
foreach loop container. So that means the task have a variable in the
source connection. This variable got the value (as an expression) of
"c:\\.....\\"+@.[User::ForeachloopVar].
But an error message appears when i run it.The message is

File System Task: An error occurred with the following error message:
"The given path's format is not supported."

When i don′t use the variable in the source connection it works fine.
Anyone knows what might be the problem?
Thanks.

Escape the slashes to be "\\"

BTW - you may find this blog post from Kirk Haselden useful ... http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/12/7750.aspx

Donald

|||I′m sory but that′s not the problem.Because i′ve just wrote the example whitout the slashes but they′re there. And i′ve already looked at that blog. It was helpfull to create my package. But thats the thing in his case the value of the source variable from the foreach loop appears in the value of the expression, mine doesn′t.|||

I see that you are creating a path and appending the source variable in an expression. However, the ForEach loop includes the option to return the fully qualified name of the file. Is there a reason you're not using that?

Donald

|||I′m using the fully qualified name option that's not the problem.|||

It′s working now i did some changes like using only name and extension option on the foreach an voilá.

thanks.

|||

That link was quite a nice example, but I seem to be having a bit of a novice problem. I've followed the blog virtually to the letter, even changing to the variable names used, but on the File System Task I get an error saying that ' FileSourcePath is used as a source or destination and is empty '.

I created the ForEach loop container as was described:

On the collection I have set the path (browsed to the path, so it does exist). I've specified files as '*.txt' - there will be 2 files in the directory by the time the container executes. In the variable mappings I have specified the user::FileSourcePath variable - it has an index value of 0. The variables have a scope of the entire package. I feel I've missed something obvious here, but somehow my container does not update the values for that variable - any help?

|||

Look i was having that same problem before.I did just like the blog shows and it alway fails. I don't know why because it seems to work in the blog. But i don't know if my solution was the best one but i created another variable to contain the flat file source path and and in the foreach loop i've chose the option on the collection pane on the retrieve file name -Name and extension. That means i'm olnly getting the *.txt for the variable @.[user::FileSourcePath]. And then i've put the new variable whit the @.[user::FileSourcePath] in the Connection Manager connectionstring. So then i've just wrote the following on the evaluate as expression of the user::FileDestinationPath

@.[User::FileDestinationFolder] + "\\" +(DT_WSTR, 10)(DT_DBdate)GETDATE()+ @.[User::FileDestinationPath]

and it rename the file just like i wanted and put the date before the old name.

Look i'm new at this thing to so i don't know if i've explained well. But i hope that you can use something.

|||

If you receive an error when the package starts saying that a source or destination placeholder variable is empty you can typically fix it this way: simply ensure that the default (initial) value of the variable is set to a filename.

Setting DelayedValidation on the loop may also help in your case.

Donald

File Operations

I need to know, how to rename a file, how to delete a file. how to create a file, all programmatically in Vista.

Thanks in advance,

Frank

If your question is specific to SSIS, you can use the File System Task. If this is a general Vista question, you might want to try another forum.

File Manipulation from MS SQL Server

I am working on an application which uses DTS to move data into temporary tables. I would like to be able to rename/relocate the source file in order to maintain a historical reference. The process which creates the source file is not flexible at all. Is there a way to manipulate the file's name and/or relocate the file by using SQL Server.

Thanks in advance!

Daniel
Austin, TexasTry looking into xp_cmdshell stored proc in BOL. This will allow you to use DOS commands from a TSQL script. We had to use this stored proc to rename delimited files we created from a DTS to be exported to a marketing company. Works pretty good.

HTH

DMW|||Hi DMW,

Thank you very much! I will give this a try.

Sincerely,

Daniel
Austin, Texas|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(RowNum int IDENTITY(1,1), Data varchar(8000))
GO

INSERT INTO myTable99(Data) EXEC master..xp_cmdshell 'Dir C:\*.*'

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||I use sp_oaxxx with FileSystemObject, because xp_cmdshell will require for a user to have privileges on the target file system.