March 3, 2008 at 11:32 am
May 16, 2008 at 2:37 pm
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?
May 16, 2008 at 2:47 pm
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.
July 1, 2008 at 8:15 am
[font="Verdana"]
Wonderfully useful information. Thank you!
😎
[/font][/size]
August 28, 2008 at 1:35 pm
What value type and value did you use for the variable?
January 9, 2009 at 4:04 pm
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
January 10, 2009 at 10:15 pm
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
Change is inevitable... Change for the better is not.
January 10, 2009 at 10:45 pm
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“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 10, 2009 at 11:02 pm
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. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2009 at 10:37 am
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
February 9, 2010 at 2:59 pm
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
February 9, 2010 at 3:37 pm
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
April 6, 2010 at 6:07 pm
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
April 7, 2010 at 7:33 am
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).
June 22, 2010 at 8:05 am
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
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply