May 19, 2009 at 8:05 am
Hello,
I need to come up with a way to add the current date to a daily .csv file name and then upload just that single file to an FTP site.
I already have an SSIS package that builds the file. Now I need to rename it with the date (20090519_data.csv) and upload it.
Any suggestions?
Thanks
May 19, 2009 at 8:22 am
Prefix the destination variable with
(DT_WSTR,4)YEAR(@[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]), 2)
For more sample date-time formats, click here
--Ramesh
May 19, 2009 at 9:15 am
Thanks for the reply Ramesh.
I'm having a hard time getting this to work. Here's what I'm attempting to do.
I've added the "User::DateTimeVar" variable to the package. Then I created a "File System Task" and tried to add your expression to the destination variable. Here's the error that I get when I try to evaluate the expression.
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
------------------------------
ADDITIONAL INFORMATION:
The function "YEAR" does not support the data type "DT_I4" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Evaluating function "YEAR" failed with error code 0xC0047089.
The function "YEAR" does not support the data type "DT_I4" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
(Microsoft.DataTransformationServices.Controls)
------------------------------
BUTTONS:
OK
------------------------------
May 19, 2009 at 9:21 am
Can you post the expression you are trying to evaluate?
--Ramesh
May 19, 2009 at 9:37 am
Here's a screen shot...
May 19, 2009 at 9:43 am
It was my bad!!! This is what happens when you post late in the night:w00t:
Change the expression to
(DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()), 2) + ".CSV"
--Ramesh
May 19, 2009 at 10:13 am
The expression evaluates now. However...I get this error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at File System Task: Failed to lock variable "D:\20090519-acct.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Any ideas?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply