August 28, 2008 at 1:24 pm
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
August 28, 2008 at 2:06 pm
Here is the solutin provided by one of sql server central member
http://www.sqlservercentral.com/Forums/Topic459991-148-1.aspx?Highlight=rename
VG
August 28, 2008 at 4:34 pm
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
September 3, 2008 at 3:02 pm
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.
September 3, 2008 at 3:24 pm
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.
September 3, 2008 at 5:12 pm
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.
September 3, 2008 at 5:48 pm
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.
September 3, 2008 at 7:29 pm
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.
September 4, 2008 at 10:01 am
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.
September 4, 2008 at 10:15 am
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. :):)
September 4, 2008 at 2:17 pm
use a VB Script to change the file name. I use the below script to do that exact same thing to an excel export from a OLE DB Source
Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim mydate As Date
Dim ExportFileName As String
mydate = DateAdd(DateInterval.Day, -1, System.DateTime.Today)
ExportFileName = "c:\" + "T" + mydate.ToString("yyyyMMdd") + ".xls"
Dts.Variables("ExportFileName").Value = ExportFileName
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
April 30, 2009 at 11:17 am
Hi, when I try to do this with the time I get the same error but when I leave the time off and only use the date it works fine? Any ideas?
September 15, 2009 at 1:09 pm
Thanks, I was looking for the answer as well.
Any idea on how to make the month two digits (ie '09') instead of just 1 ('9')?
March 24, 2014 at 4:39 am
I have the same error for my SSIS package using the File System Task.
It returns this error :
[File System Task] Error: An error occurred with the following error message:
"The given path's format is not supported."
Steps done:
I checked my variables and find out that my destination file has a special character which is not allowed in excel filenames.
Therefore, we still need to check the filename itself 🙂
In my case, I removed colon (:) in my filename and it works like a charm!
😀
March 1, 2017 at 3:22 pm
SSIS\SSRS\SSAS - Thursday, August 28, 2008 4:34 PMERIN 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 ForeachLoop2.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+ extension4.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 choiceThen drag Foreachloop container , configure as for eah file found return only file nameThen map the variable file name.Bring the File system task and for destination true as varaiable give the fullarchivepathand source as varaibale fullsourcevariableLet me know if u need more hintsthanks
Hi ..
I do have a similar case to develop. But the difference is in source path I do have more than 10 files with different names and same extension. I want to rename file extension only for 5 files .
Let say for example:
Source : ABC.inx , ABD.inx, ABE.inx
Destination : ABC.dat, ABD.dat, ABE.dat
What is my difficulty is I can put only one at a time in filename variable as per your solution .....is there any way to put logic or anything to select multiple files and rename it in once. And selecting 5 require file from 10 files at source..
Please reply me any suggestion on it
Thanks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply