Flat File Destination - File name

Hello,

I'm trying to send a file with FTP.

In the Flat File connection manager I have to write the name of the file.

Is there any way of deciding at runtime what the name of the file should be?

Thank you.

Yes. Use expressions.

http://blogs.conchango.com/jamiethomson/archive/tags/Expressions/default.aspx

-Jamie

|||

Hi Chaps

I am trying to achieve a similar thing with the FTP Task
ie incorporate the date into the filename when sending the file.

I have set IsRemotePathVariable=True

I have created an expression for the RemotePath:
"/FName"+ (DT_WSTR,4)DatePart("yyyy", GetDate())
+ (DT_WSTR,2)DatePart("mm", GetDate())
+ (DT_WSTR,2)DatePart("dd", GetDate())+".txt"

However, an error is produced:
"Error at FTP Task: Failed to lock variable"/FName2007611.txt"

Should I be linking the expression to a variable ?
and if so how

Any guidance would be very much appreciated.

Regards

JohnJames
|||

Hi,

Just so I understand this correctly, do you use a hard coded file name in the Data Flow Flat File Destination (f.ex. outputfile.txt) and then afterwards in the Control Flow you change the file name (f.ex. outputfile_11_06_2007.txt) by using these expressions?

Thank you.

|||Yes

In the Dataflow I am using a hard-coded filename,
to extract data from SQL Server to a flatfile.
In the Control Flow is the FTP Task where I am trying to add
the Date to filename using an expression.

Regards

JohnJames
|||Why don't you create a variable who's value is an expression. You then use the variable in the data flow and the FTP task. That way you are always guaranteed that the file name is correct.
Saves you from having to rename a file as well..|||Thanks Crispin

I'll give a go and let you know

Regards

JohnJames|||

Hi,

I'm trying to decide the filename of my Flat File Destination at run time by using an expression variable.

I created an expression variable:

Name: FileName_Expression Value: C:\test.txt

However, in the Data Flow in Flat File connection manager, I can't seem to put my variable name inside the "File Name" editbox and I have no idea where to put my expression variable.

Any ideas?

Thank you so much!!!

|||

Mr. Hat,

Use the connectionstring property of the flat file connection manager.

ex. ConnectionString = @.[User::myPath] + "\\ERROR_" + @.[User::FileName_Expression]

(Where myPath is something similar to C:\temp)

NOTE: new questions like this should probably get a new thread as many people will just look past the threads which appear to have already been answered.

Hope this helps.

EWisdahl

|||Wow, it worked! Thanks a lot!! |||

Hi

when i'm trying to set the file name runtime for excel destination by setting expression

ConnectionString : @.[User::ExcelFullFileName]

but getting below error

Error at Data Flow Task [Excel Destination [7689]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

please can you advice regarding this?|||I believe (but I may be wrong) that the Excel file needs to exist to be referenced by the Connection Manager.sql