Dynamic File Name from an dstx project

[kxramse]

I am on my first SSIS project and I am kinda learning as I go. The wizard has worked great, I got my SQL statement into the .dtsx project and into a flat file, with the extension I need as well. However what I really want is to have a file name that is composed of different variables. I actually have a small SQL statement that can generate the file name:

select (
		rtrim(vendorID)
		+ '_'
		+ cast((year(LastRunDate)) as Char (4)) 
		+ case
			when (Month(LastRunDate)<=9) then
				'0' + cast((Month(LastRunDate)) as varchar (2))
			else cast((Month(LastRunDate)) as varchar (2))
			end
		+ case
			when (day(LastRunDate)<=9) then
				'0' + cast((Day(lastRunDate)) as varchar(2))
			else
				cast((Day(LastRunDate)) as varchar (2))
			end
		+ '_'
		+ rtrim(CampaignTrackingCode)
		+ '_'
		+'3'
		+ '_'
		+ rtrim( 
		  case
				when VersionNumber <=9 then
					'00' + cast(rtrim(VersionNumber) as char(3))
				when VersionNumber<=99 then
					'0' + cast(rtrim(VersionNumber) as char(3))				
				else
					cast(rtrim(VersionNumber) as char(3))
				end
			   )
		+ '.'
		+ FileExtension
		) 
from stmReceived

I have my data flow task working great, except I can't figure out how to tell it to accept a dynamic name. I'm gathering that this has something to do wtih running a "execute SQL task" and passing my SQL into an "expression" or perhaps a "parameter."

I might have to use a "file system task" to rename the static file name after the "data flow task" has finished creating the same named file everytime it runs? Is that the right direction to keep learning?

Is there a way to send that SQL statement into the data flow task and then into the file name, even though that SQL statement is different than the SQL that builds the actual data file?

Ok... I have to get it done pretty quick. I just need someone to point me into the right direction. Perhaps someone saw a nice tuturial on this somewhere? I can't find any. What I have found through google has been all over the map. Nothing too coherant, perhaps I'm using the wrong keywords.

Anyway, anyone know which way I should go?

Thanks,
Keith

 

[kxramse]

I'm thrilled to have my blunders help someone else!

I never actually posted the FAQ because I wanted feedback. I don't have time to check #20. If you are certain that step isn't needed I'll post it. Do you mind that I confirm that?

 

[bt7403]

GO ahead and confirm #20 will not work.

 

[kxramse]

If you are asking me to confirm, rather than saying I should consider it confirmed, the problem with my confirming is that I've switched roles and don't have access to that package.

I can put in the FAQ that it seems that step is unnecessary, but that I do not have confirmation. How about I just ask that you clarify your response?

 

[bt7403]

I have confirmed the use of item #20 per FAQ above does not work based on use of Connection property along w/ DynamiceFilename. The recommended step# 20 does not indicate property to set,so I assumed Connection was expected.
Anyway, pls. update FAQ and post completed.

thxx..