February 20, 2006 at 7:00 pm
I want to rename a file to embed the date/time into it, so that each time the package runs a new file is created. For example MyFile_20060221_150000.xls.
I have no doubt I have to make the destination file property a variable with "evaluate expression" true but I can't seem to get the expression right.
I have tried
"MyFile_" + GetDate() + ".xls"
and
"MyFile_" + Cast(GetDate() as VarChar(20)) + ".xls"
Both failed. Any ideas?
Also. when is entered a full path such as "C:\My Folder\MyFile.xls" as the expression it too failed. Is there some sort of escape character that I don't know about.
In general, I haven't managed to find the information that I need as to how these expressions should be formatted. Some practical examples would probably help a lot of people.
Dick Campbell
February 20, 2006 at 8:42 pm
You can use CONVERT(char(8),getdate(),112) to get the date part.
For time use the DATEPART function, eg.
CAST(DATEPART(hh,getdate()) as varchar)
Replace 'hh' with 'n' for minutes and 'ss' for seconds. You will have to write code to pad these with zeroes, maybe using CASE statements, like:
CASE WHEN DATEPART(hh,getdate()) < 10 THEN '0' ELSE '' END + CAST(DATEPART(hh,getdate()) as varchar)
You could also write a function to return the format that you want with the date as a parameter.
February 20, 2006 at 8:52 pm
I think that the probelm is the you can only use limited TSQL in the expression. If Cast() doesn't work I doubt that Convert(0 would work either.
Thanks for the reply.
February 21, 2006 at 5:03 am
To partly answer my own question Help seems to suggest that Cast is more C# like than TSQL.
"MyFile_" + (DT_STR)GetDate()
I couldn't get it to work though.
I had already tried the C# escape code (which I seem to recall is @) to parse a file path
@"C:\My Folder\MyFile.xls" but that didn't work either.
In this .Net Framework/CLR world it is hard to know whether to use TSQL, C# or whatever. The functions listed in help for SSIS expressions all seemed to TSQL based until I came accross the Cast example.
Dick Campbell
February 21, 2006 at 6:15 am
This worked:
"C:\\Documents and Settings\\fritcheyg\\My Documents\\Visual Studio 2005\\Projects\\SSIS\\Chapter6\\test_" + (DT_WSTR,2)DATEPART("Hh",GETDATE()) + (DT_WSTR,2)DATEPART("mi",GETDATE()) + ".txt"
That's set in the ConnectionString property.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2006 at 12:32 pm
Thanks Grant,
I got to something similar late last night.
"C:\\My Folder\\MyFile" + (DT_STR,50,1252)GetDate() + ".xls" worked although it doesn't produce a legal file name. I finally figured that "\\" was the escape sequence for "\".
Using date part as you suggest will produce a legal file name. I will be looking for "_yymmdd_hhmmss" which will be a very long expression. What a pity we can't use C# type formatting.
Dick Campbell
February 21, 2006 at 5:37 pm
Just to finish this off, here is the final result.
"C:\\Data\\SSIS\\AlarmSummary_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + "-" + (DT_WSTR,2)DatePart("mm", GetDate()) + "-" + (DT_WSTR,2)DatePart("dd", GetDate()) + "_" + (DT_WSTR,2)DatePart("Hh", GetDate()) + "_" + (DT_WSTR,2)DatePart("mi", GetDate()) + "_" +(DT_WSTR,2)DatePart("ss", GetDate()) + ".xls"
Rather complex as I was expecting. It's a pity that there isn't a simpler date formatting option or a function that returns a date/time in a format that can be used in file name. A random number generator might be another option I suppose.
Editing something this complex in the properties form is almost impossible so I did it in Notepad. It can also be difficult to spot errors in the expression.
Thanks to everyone for their help.
Dick Campbell
February 22, 2006 at 2:12 am
Yeah, building property expressions on variables is a pain. SP1 contains an expression editor that makes this a heck of alot easier.
In the meantime, the best method is to build the property expression using the expression editor attached to another property in the package of type string. I invariably build it against the Description property of a container as you can't really do any damage by doing this. Its easier than using Notepad anyway.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 22, 2006 at 4:20 am
It had occured to me that the expression builder should be available for this property and I did think of building it somewhere else as you suggest.
It would be REALLY nice if you could define custom expressions in C# or VB and access them through the expression builder. I could have used the C# .ToString() function to make this much easier. No doubt some people would get carried away and write very complex expressions though.
February 22, 2006 at 4:30 am
I agree. Building your own expression library would be really useful. Have you requested it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx)?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 22, 2006 at 5:27 am
March 13, 2006 at 2:00 pm
I am trying to do something very similar, but I am not sure how to go about creating destination file property a variable with "evaluate expression" true. Can somebody point me to a sample or help?
Thanks in advance,
Gita
March 13, 2006 at 2:15 pm
Gita,
Select the variable in the Variables pane and press F4. You will see the EvaluateAsExpression property in the properties pane.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
March 27, 2006 at 3:29 pm
I need to have the expression always evaluate to a 2-character month and day, my current filename evaluates to seg_idem_20060327.txt:
"C:\\Local\\DataIn\\SEG_IDEM_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + (DT_WSTR,2)DatePart("mm", GetDate()) + (DT_WSTR,2)DatePart("dd", GetDate()) + ".txt"
Thanks, Mary
March 27, 2006 at 3:35 pm
Mary,
I don't understand. You say it currently comes out as seg_idem_20060327.txt. That looks as though it has a 2-character month to me.
What do you want it to look like?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply