Flatfile Destination Variable Filename

Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.You can use expressions on the flat file destination to change the location of the file.|||Thank you so much for your quick reply. I have the flat file destination highlighted but there are no properties for Expressions. I also looked in the connection properties also and could not locate a place to do this. I ended up writing a vb script but I would like to have the ability to do it graphically. The SSIS team definately needs to set up a way to set Global Variables (which could contain file path settings) via a sql statement. They also need variable file pathes exactly like the raw file.|||When you right-click on the flat file connection manager, you can select properties. In there is an Expressions property. You'll want to set the ConnectionString property to the full path containing the file.|||Oh awesome! Thanks Phil.

Flat File Destination Filename with Date ?

Is there a way to append a date at the end of the filename for whenever a Flat File Destination is run? So far, I only see the ability to browse to a static filename.For the Flat File Connection, which is being used by the Flat File Destination, you can set the ConnectionString using Expressions, to set your file name with dates.|||Do you have any resources on syntax for this?|||

"C:\\file" + (DT_WSTR, 2)month(getdate()) + (DT_WSTR, 2)day(getdate()) + (DT_WSTR,4)year(getdate()) + ".txt"

For example, if i use the above expression for Connection string of the Flat file connection, it will create a file

C:\file12192005.txt

if i run it today.

|||Are you talking about File Connection Manager when you click browse to specify the File Path? If I put that in there of course it doesn't like it. Where exactly are you placing in this script, what component, what tab, what property?|||

For the Flat File Connection Manager, on Properties tab, you see Expressions, when you click there, you can set expressions for some of the properties for the Flat File Connection Manager. In this case, I am asking you to set the ConnectionString property as an expression.

you should see

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

in BOL for further details.

|||Oh, I see, I was double-clicking the file connection manager using the properties there, I didn't think to look at the VS properties pane in VS, thanks!|||

either I'm missing something or this link doesn't work:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

|||Open BOL and paste this Url in the url box. It is not a http link.sql

Flat File Connection Manager

As I have a file whose delimiters can be different. I have need to change the delimiters and filename programatically.

I have come across a way to change the filename using the inbuilt expressions setter. Howver the columns are not listed, possibly because they are on the advanced tab and theoretically each column delimiter could be different.

Will I have to do this through a script or is there an easier method?

As far as I know, this would need need to be handled by a script or a derived column component. You'd need to bring the flat file row in as a single column, then split it up yourself. You could reference a variable in the script or derived column component to determine what delimiter to use.

Here's a few posts that might help with this:

http://blogs.conchango.com/jamiethomson/archive/2006/07/14/4226.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/15/handling-varying-columns-part-2.aspx