Hello,

This is for SSIS 2008r2. I am generating flat files successfully with a datetime stamp (filename_yyyymmddhhmm). Now I need to append a MAX(FILEDATE) from the file. I have a query to do this, but am not sure about two things:

1) Is it advised to put the query in a Script Task (with db conn and so forth) or is it better to put it in an Execute SQL Task? I am thinking the latter but am not 100% sure.

2) How would I pass the result of this query (yyyymm) to the filename. The filename format would be (filename_yyyymm). I am assuming that I would probably need to pass the result into a variable/expression but am not quite sure of the steps involved. I've Googled this a bit and have tried a couple things but am not clear on a definitive solution.

Any help would be greatly appreciated as always.

Thanks and Regards,

1. You can put the query in a execute sql task. Give resultset option as single row and give an alias for maxdate in the query. Then go to resultset tab and map the maxdate alias to a variable of type date created in SSIS. Then add another variable for filename and set EvaluateAsExpression true for it and set an expression like below for it

"filename_" + REPLACE(SUBSTRING((DT_WSTR,30)(DT_DBDATE)@[User::MaxDate],1,7),"-","")

Where MaxDate is the variable you used to store value fro execute sql task

Now use this filename variable to set connection string property by appending the path.

Hi Visakh,

Thanks for your quick response. I've followed the steps and got what you're saying. I'm good until I get to the last part...

"Now use this filename variable to set connection string property by appending the path."

I understand that this is the connection in the Execute SQL Task in the General tab, but am not sure how to append the path. Can you please show quick example of how I would append the path and how this should look?

Sorry for the lame newbie question, but I am excited to see this work. This is a great learning question.

Thanks,

Buster