Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

to rename a file by attaching datestamp at the end of filename using SSIS File task Expand / Collapse
Author
Message
Posted Monday, March 3, 2008 11:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:38 AM
Points: 1,157, Visits: 3,270
LOL

that's what I thought :)


Tommy

Post #463318
Posted Friday, May 16, 2008 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 15, 2010 8:15 AM
Points: 4, Visits: 63
Hi I am very new to SSIS. Can you please explain where I have to use this simple solution. In the Script task when use code given by Tommy it is showing the error at File.Move. Is file varaible or any other function?

Post #502362
Posted Friday, May 16, 2008 2:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:58 AM
Points: 2,280, Visits: 3,069
You have to use the code in the expression editor. You have to view the properties of the object you want to use the expression in. Click the ellipses (...) and build your expression on whatever property you want. For a dynamic filename, you have to do it on the connection string property.



My blog: http://jahaines.blogspot.com
Post #502365
Posted Tuesday, July 1, 2008 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:17 AM
Points: 2, Visits: 127
[size="7"]
Wonderfully useful information. Thank you!

[/size]
Post #526664
Posted Thursday, August 28, 2008 1:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:42 PM
Points: 1,155, Visits: 549
What value type and value did you use for the variable?
Post #560780
Posted Friday, January 9, 2009 4:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:26 AM
Points: 3, Visits: 32
Hello all,

There is a simpler way I think for providing the new filename that you want to rename your files to wihtout the need for a connection manager or arcane .NET scripts:

- Assuming you put your task inside a ForEachLoop with a Collection of type ForEachFileEnumerator which maps a Variable [User::FileName] for the current filename of each file:

On the FileSystemTask Properties do this:
1. SourceConnection Section: IsSourcePathVariable = TRUE, SourceVariable = User::FileName ( This is the variable you get from the ForEachLoop Container)
2. DestinationConnection: IsSourcePathVariable = TRUE, DestinationVariable = User::NewFileName ( I just made this up, use whatever name you like)

Now the fun part:
- Each variable can be replaced by an Expression exactly as you do with various Properties of Tasks andTransformations. Here's how:
1. Open the Variables Window and Click on the User::NerFileName Variable. ( NOTE: YOu must have the FileSystemTask selected in the Control Flow since the Variables Window refreshes with each selection to show only Variables that are in scope).
2. An alternative way to Click on that variable is via the Tree in the Package Explorer Tab. Find the node of your FileSystemTask and expand it to see its Variables subfolder.
3. Assuming you have clicked on the Variable with one of the two methods above, try looking inside the Properties window. It is usually docked on the bottom right part of the IDE. These are the Properties of the Variable you have selected. Nifty heh?
4. Now do this: EvaluateAsExpression = TRUE.
5. Now give the Expression to the next Property named Expression.
6. Here is a sample Expression for a Timestamp which assumes a .dat extension. Customise it as you like:
REPLACE( @[User::FileName] , ".dat", "_" + (DT_WSTR, 4) YEAR( @[System::StartTime]) + "_" + (DT_WSTR, 2) MONTH( @[System::StartTime]) + "_" + (DT_WSTR, 2) DAY( @[System::StartTime]) + ".dat" )

That's all. No connection manager needed. No need to use PAckage Configurations also. Just a variable. When SSIS wants to use it is evaluates the Expression. All the other Variables that the Expression Uses are in Scope. User::Filename belongs to the FileSystemTask's parent Container (the ForEachLoop COntainer) and the System::StartTime belongs to the Package.

Regards,
ArthurDent
Post #633976
Posted Saturday, January 10, 2009 10:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Heh... of course, the simplest of all the solutions would be to do it all in T-SQL and perhaps a touch of BCP... SSIS not required. ;)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #634287
Posted Saturday, January 10, 2009 10:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:02 AM
Points: 2,049, Visits: 3,596
Jeff Moden (1/10/2009)
Heh... of course, the simplest of all the solutions would be to do it all in T-SQL and perhaps a touch of BCP... SSIS not required. ;)


You are hard core!


...it's a good thing!:D


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #634294
Posted Saturday, January 10, 2009 11:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Heh... nah... I just hate GUI's... too limiting... can only do what they were designed to do using only the ways they were designed to do it... unless you write a script which defeats the whole bloody purpose of the GUI to begin with. :P

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #634296
Posted Thursday, July 30, 2009 10:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:27 AM
Points: 10, Visits: 444
The easier and preferred method is to simply create a variable for the path and use get date functions. Click the connection manager for the output text file --> View the properites --> expand "Expressions" and modifiy the connection string as follows:

Note: @[User::SharePath] is a variable that holds the path.

@[User::SharePath] +
RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + ".txt"
-----------------------------------------------------------------------------------------
Hi, I tested this, this is working fine. do I need to create "File system task" to do this?
basically I want to rename the file.

ex:C:\hello.txt to c:\hello_2009-July-30.txt

I mean filename+todays date.txt

Thank you,
Jyosthna
Post #762584
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse