SSIS - Dynamic File Name for FTP Component

Question

I'm trying to create a data flow in SSIS to help me upload a file via FTP and I'm having a bit of trouble. The name of the file needs to be dynamic, in the format of filename_mmddyy.xls. So every day it applies the date and uploads a new file.

I was able to make it so that the file would save correctly in a data flow with the following expression:

@[User::path]+ 
Right("0"+(DT_STR,4,1252)DatePart("m",getdate()),2)+
Right("0"+(DT_STR,4,1252)DatePart("d",getdate()),2)+
Right("0"+(DT_STR,4,1252)DatePart("yyyy",getdate()),2)+
".xls"

The FTP component, however, won't take this as an expression for the LocalPath. I tried to overwrite my User::path variable inside of a Script Component in my original data flow, but that didn't seem to work either.

I know I'm missing something every simple, but I've stared at it long enough and it's just not coming to me.

Thanks!

EDIT

Ok, so after fooling around for another day, here is what I've come up with:

I made my original task to create my flat file. Side note, my vendor actually needed a true excel xls and not a csv, so I had to make a file connection destination, which then required me to make a static file on my server.

I then used a script task (C#) to copy the static file to a file with the dynamic name. Of course, I have to leave the original file because the excel destination needs to have that there.

Finally, I made another file connection and used the above expression as it's connection string. I then have the FTP task use the file connection as the file to upload.

I'm going to try and trim it all down a bit. I think I can get away with not using the script task and instead using a file system task to copy and using the file connection to help with the dynamic path name.

Bottom line I've learned is this: In SSIS, when it says "PathIsVariable", what you put in that text field is taken as a variable. So if I put in @[User::path], it resolves to "C:\pathonmydrive". SSIS then looks for a variable with THAT name, ie C:\pathonmydrive, to find the file. It's a little counter-intuivite, but now that I know I can avoid that pitfall.

Answer 1

1. Create a new variable, e.g., FilePath. Use string as data type and in the variable's properties, set EvaluateAsExpression to true.
2. Configure the expression of the variable: @[User::path] + "filename_" + Right("0"+(DT_STR,2,1252)DatePart("m",getdate()),2) + Right("0"+(DT_STR,2,1252)DatePart("d",getdate()),2)+ Right("0"+(DT_STR,4,1252)DatePart("yy",getdate()),2) + ".xls"
3. Use the Evaluate Expression button in the expression editor to see if the expression resolves the correct path to the file.
4. Modify the Expressions property of your FTP task and set the variable you created under LocalPath.

Answer 2

Create a new variable, e.g., FilePath. Use string as data type and in the variable's properties, set EvaluateAsExpression to true. Configure the expression of the variable:

@[User::path] + "filename_" + Right("0"+(DT_STR,2,1252)DatePart("m",getdate()),2) + Right("0"+(DT_STR,2,1252)DatePart("d",getdate()),2)+ Right("0"+(DT_STR,4,1252)DatePart("yy",getdate()),2) + ".xls"

Use the Evaluate Expression button in the expression editor to see if the expression resolves the correct path to the file.

Modify the Expressions property of your FTP task and set the variable you created under LocalPath.

---

I have Executed above way, but still I have issue The variable cannot be found...Any thoughts

Answer 3

In order to change a variable value inside a script component, you need to first pass the variable as ReadWrite:

Then, to set a new value to the variable:

Dts.Variables["folders"].Value = "value you want to assign";