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 Tuesday, February 9, 2010 2:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:03 PM
Points: 17, Visits: 86
How would I get the previous month?

I need my file to be renamed Mods201001.xls.

Here's what I have

@[User::SharePath] +
RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+
RIGHT("0" + (DT_STR,4,1252) DatePart("m", getdate())-1,2)

But I get an error -
Attempt to set the result type of binary operation ""0" + (DT_STR4,1252)DatePart("m",GETDATE))-1" failed with error code 0xC0047081
Post #862761
Posted Tuesday, February 9, 2010 3:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:03 PM
Points: 17, Visits: 86
Tommy -

I've also tried your solution but I'm getting the red x in the script task.

The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.

I copied your code and put in my file name and file path but I get that error, any ideas on the cause?

Thanks
Post #862785
Posted Tuesday, April 6, 2010 6:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 25, 2013 3:19 AM
Points: 1, Visits: 18

I wish to strip the date from a filename that is generated from another system so that I can run a schedule DTSX packages on the file. The file is in the format SampleFile_<yyyymmdd> <hhmmss>.csv. I'd like to back this up to another folder but also rename it to a static value such as SampleFile_Current.csv so that my DTSX package can run as it depends on the filename.

Any suggestions welcome.

thanks
MC
Post #898031
Posted Wednesday, April 7, 2010 7:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:25 PM
Points: 254, Visits: 755
One thing to keep in mind is that breakpoints will cause that expression-based variable to be re-evaluated at the time that you step past the break point. If you need to reference that same value (in this example, if you add an expression-derived variable in the name of a file before the break point and then the variable-named file name is again used in a later/subsequent task after the break point) could potentially be different because of that re-evaluation.

Granted you'll probably only see this if you include seconds as part of the stamp, but if the before/after time of the break point spans over a minute change (or going past midnight, impacting the date, also), the variable will be re-evaluated and change.

A safer, more static approach it to set variable in a script task in the control flow, which keeps that value constant throughout the package or container (until you perhaps re-run that task in a loop).
Post #898502
Posted Tuesday, June 22, 2010 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 5:55 AM
Points: 4, Visits: 67
Hi Adam, this is brillient, just what I was looking for too...well, almost.

I just need to put a small spanner in the works. What is the best way to give the file name yesterday's date? I have tried fitting "DATEADD(m, - 1, { fn CURDATE() })" into your script but it aint having it. Any ideas?

Regards,

Ged
Post #941066
Posted Tuesday, July 6, 2010 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 6, 2010 2:13 PM
Points: 1, Visits: 1
Hello - I just created an account so that I could thank Adam and Tommy for this thread - I adapted the code to put hours minutes and seconds in too but other than that it was just what I was looking for (when I found how to edit the connection string expression in VS2008!)

Thank you :)

James
Post #947816
Posted Wednesday, August 18, 2010 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 6:04 AM
Points: 8, Visits: 157
Adam,

Thanks for the nice streamlined solution. Works like a charm.
Post #971471
Posted Friday, March 29, 2013 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:05 PM
Points: 1, Visits: 25
I am trying to accomplish basically the same thing, but am having big issues with it:

using SQL 2005 to run SSIS and export file name myfile_.csv and placing it in folder on the same server "c:\apps\SalesExport\myfile.csv

This works great, but now I either need to rename the file on the way out or after the fact to add datetime stamp like myfile_031303291245.csv. I am lost, been working on this for several days, any help would be great, thanks
Post #1437081
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse