SSIS Parameters With Functions

Question

I've done quite a bit of searching on this but haven't been able to come up with a solid solution. I have a use case where for example I want to include the date in the file name, let's say I use the following formula:

"EmployeeCount_"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2)  + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +".csv"

This works out great as a variable, but what if I want to pass this function as a parameter from SSMS? Is there a way for Parameters to be evaluated as a expression? I attempted passing the parameter into a variable to achieve the desired effect to no available.

Am I missing something here, or should I just be hard coding this into a variable / calculating this elsewhere and pulling it into SSIS from a table?

score 0 · Answer 1 · answered Mar 29 '17 at 20:57

0

If you want to retrieve the value from a database function and store in a variable you can use the Execute SQL Task.

You can combine variables with expressions in other variables along with scripts. Where you perform the calculation is your choice.

answered Mar 29 '17 at 20:57

mheptinstall

2,109
3
24
44

score 0 · Answer 2 · answered Mar 30 '17 at 03:15

First you have to create 2 variables p_Date of type DateTime and Filename of type String

In the properties tab set EvaluateAsExpressions = True for the variable Filename and Use the following expressions

"EmployeeCount_"+(DT_STR,4,1252)DATEPART( "yyyy" , @[User::p_Date]  )+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" ,  @[User::p_Date]  ), 2)  + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" ,  @[User::p_Date] ), 2) +".csv"