SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rename File in SSIS


Rename File in SSIS

Author
Message
Erin.
Erin.
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2011 Visits: 558
I need to rename a file in SSIS. Part of the file rename should include the current date.

I am trying to use the File System Task, but I don't know how I can rename the file. The file name should look like the following: TimeCommUpdatesyyyymmdd.xls

I'm thinking I may have to use expressions, but I can't find any good documentation on the subject.

Any help would be appreciated.

Thanks,
Erin
VG-619426
VG-619426
SSC Eights!
SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)

Group: General Forum Members
Points: 992 Visits: 692
Here is the solutin provided by one of sql server central member

http://www.sqlservercentral.com/Forums/Topic459991-148-1.aspx?Highlight=rename

VG
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 565
ERIN i would recommed you to use variables and expressions to do it.
First create 6 variables--
1.filename - abc (what ever u want, we will pull the value from ForeachLoop

2.SourcePath - give the path name as D:\folder\folder\

3.SourceFullfilepath - put this in expression(f4 + evalate as expression and then build the expression with source path + filename+ extension

4.ArchivePath- give the path D:\folder\folder\Archive\

5.ArchiveFullfilePath- put this one in expression as
@[User::ArchivePath] +  @[User::FileName] + (DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2)+"_" + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GetDate()), 2) + @[User::FileExtension] 


6.FileExtension- extension of ur choice

Then drag Foreachloop container , configure as for eah file found return only file name

Then map the variable file name.
Bring the File system task and for destination true as varaiable give the fullarchivepath
and source as varaibale fullsourcevariable

Let me know if u need more hints
thanks
sonia-834492
sonia-834492
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 176
i am trying to do the same but here i have to rename multiple files in a folder with the current date. when i execute the package i get the following error:

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

Can somebody suggest me when I am going wrong. I verified all the expressions used for the variables in the file path names and they look good.
sonia-834492
sonia-834492
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 176
for the above, i executed the file sytem task for renaming for a single file without using the foreach loop container and it works fine but when i put the task into the container then it is giving the above mentioned error. please suggest me where i might be going wrong with the foreach loop container.
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 565
sonia its clearly says that the path format is not supported. I am doing all my renaming copy and archive all the time like this , it works. may be you have something wrong in the return. only return file name and use a extension in the variable.
sonia-834492
sonia-834492
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 176
hi gyanendra,

i have specified all the values in my tasks. can you go over them and let me know where the error might be. i checked the path names for all the expressions and they look correct to me.

the files- file1.txt,file2.txt and file3.txt are in sample folder( source) and need to renamed and moved to archive folder(dest)

These are the variables that i am using for this job:


foreach loop container:
enumerator: for each file enumerator
folder = d:\sample
files = *.txt
variables mapping = user::myfilevalue

file system task:
isdestinationpathvariable = true
destinationvariable = User::fulldestpathfilename
operation = rename
issourcepathvariable = true
sourcevariable = User::fullsourcepathfilename

variables:

myfilevalue = file1.txt
dest path = d:\archive
source path = d:\sample
fullsourcefilepathname = @[user:: sourcepath] + @[user:: myfilevalue]
fulldestfilepathname = @[user:: destpath] + substring( @[User::myfilevalue],1,findstring( @ [User::myfilevalue],".",1)-1) + "-" + (DT_WSTR,2)month( @[System::StartTime]) + (DT_WSTR,2) day( @[System::StartTime]) + (DT_WSTR,4)year( @[System::StartTime]) + substring( @[User::myfilevalue], findstring( @[User::myfilevalue], ".",1) , LEN( @[User::myfilevalue] ))

the above is supposed to give a result of file1932008.txt,file2932008.txt and file3932008.txt

i know there is some very simple error involved and this is supposed to be an easy one but somehow cant resolve it.
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 565
First of all , I don't know what you are returning from foreachloop conatiner-- fullpathfile, or name and extention, or just name. If i were you i would go just return teh file name in the for each loop container( you know what i am takling rt) there is a option where what you want to specify what to return back and then map this to a variable. and then create a variable called "fileExtention" and assign the value that corresponds to your extension choice like ".txt"

then this way you avoid using substring function in the archivedestination. Then i am not sure your other function to get date works or not. ( i am hoping it is ) Then also make sure ur 2 variables for arhive destination and source are using expressions. highlight the variable and then press f4 , it will open the expreeion builder for variables and set evalute as expression as true and then build your expression over there with adding file extension at the end. in the expression for source
bring your source variable + file name( name return from foreach loop) + extension and for archive
build expression as archivefolder variable + filename + all the functions that will return date portion + file extension

this should work out.
sonia-834492
sonia-834492
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 176
i created a fresh one and did it in the way you had mentioned earlier in one of your posts by creating 6 variables, but still i get the same error. i need to make sure of one thing. in the foreach loop container, for the variable mapping section -- here do we just give some arbitary name or the exact file name of one of the files in the folder. i tired both ways but still getting the same error.

the file system task works for a single file. so i think there must be something wrong with the foreach loop container.

i appreciate your help in this matter.
sonia-834492
sonia-834492
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 176
It finally worked for me. I figured out the reason for my error. in the foreach loop container for the retrive file name section, I had checked fully qualified option. when I changed it to name and extension, it worked. I am able to rename and move my files to archive folder. SmileSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search