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


Rename File in SSIS


Rename File in SSIS

Author
Message
OklahomaSooners
OklahomaSooners
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 27
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
M Roush
M Roush
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 86
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?
alton_d
alton_d
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 35
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')?
subscriptionemail
subscriptionemail
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 120
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 Smile
In my case, I removed colon (Smile in my filename and it works like a charm!

:-D
mayurpatel.ce
mayurpatel.ce
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 26
SSIS\SSRS\SSAS - Thursday, August 28, 2008 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 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.
Thom A
Thom A
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18175 Visits: 8162
mayurpatel.ce - Wednesday, March 1, 2017 3:22 PM
SSIS\SSRS\SSAS - Thursday, August 28, 2008 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 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.


This is a nine year old topic you're replying to. I would suggest starting a topic for your own needs there. Things have moved on quite a bit since 2008.

In that topic, could you answer why is it a problem renaming each file sequentially in a For Each loop? It's a very quick process, and if you could rename all the files simultaneously, you'd still have contentions on your I/O device.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
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