hi all i have a data flow in for loop container,and the for loop is configured to loop 3 times, in the data flow data moves from table to a flat file, each time the flat file is created dynamically with a datatimestamp making the file a unique, but it is weird to see for the first loop the flat file is created and and data goes, second loop goes into the same flatfile, looks like the expression written for the dynamic flat file is not evaluating hence the data is goin in to the same flat file , i want to know if in for loop we can use a dataflow task which creates flat files dyamically (a new flat file with time stamp for every time it loops through) Please share your thoughts.

If u are creating a file dynamically with timestamp u need to include hours, minutes and seconds also in the flat file. If u just have year, month and day it will override. U might want to create dynamic connection something like "FileName_yyyymmdd_hhmmss.txt"

yes i do have hrs minutes seconds in my time stamp.

hi all i have a data flow in for loop container,and the for loop is configured to loop 3 times, in the data flow data moves from table to a flat file, each time the flat file is created dynamically with a datatimestamp making the file a unique, but it is weird to see for the first loop the flat file is created and and data goes, second loop goes into the same flatfile, looks like the expression written for the dynamic flat file is not evaluating hence the data is goin in to the same flat file , i want to know if in for loop we can use a dataflow task which creates flat files dyamically (a new flat file with time stamp for every time it loops through) Please share your thoughts. Yes, this should work fine. I would guess that you're not quite setting the expression on the connection manager correctly. Can you share more details about how you are attempting to do that? Perhaps provide some screenshots?http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

hi jamie, to figure this out i declared a vriable timestamp1 package level and timestamp2 dataflow level , timestamp2 which is a dataflow level is contained in for loop, i had a break point on data flow post exec, first loop timestamp1 (package level ) turned to "100601" and timestamp2 also to "100601" (mmhrss),second loop timestamp1 changed to "100741" but timestamp2 remained at "100601" , timestamp1 kept changing but timestamp2 remained same through out the for loop, i used this expression (DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) + RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) suggest please.......

Are you saying that you have variables called [timestamp1] & [timestamp2]? The first thing that occurs to me is that neither of those variables are referenced in your expression.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

yes i have variables timestamp1 and timestamp2, where i have the expression which i gave above. basically my flat file connection string has expression some thing like this @destinationpath +"\converted_" + @timestamp2 +".txt", so the point is the timestamp2 is not changing for every for loop which is at package level, so i though i will use the variable timestamp1 which is declared at data flow level, but this variable is not accessible in the flat file expression ....,

yes i have variables timestamp1 and timestamp2, where i have the expression which i gave above. basically my flat file connection string has expression some thing like this @destinationpath +"\converted_" + @timestamp2 +".txt", so the point is the timestamp2 is not changing for every for loop which is at package level, so i though i will use the variable timestamp1 which is declared at data flow level, but this variable is not accessible in the flat file expression ...., Aha, I think I know what the problem is. I assume that [timestamp2] is scoped to the package, correct? If so, any reference to @[System::ContainerStartTime] will be referring to the @[System::ContainerStartTime] variable that is scoped to the package container. The value of that variable never changes (that is to say, the start time of the package never changes) and hence that expression (and hence @[timestamp2]) will always return the same value regardless of when you refer to it in the same execution. http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

so what is the solution now?, i have to use a variable at package level only because variable at data flow levels are not accessible to flatfile connection string expression, do i need to change the container start time? if so to what?

so what is the solution now?, i have to use a variable at package level only because variable at data flow levels are not accessible to flatfile connection string expression, do i need to change the container start time? if so to what? One possible solution would be to use a Script Task to assign a new value to @[timestamp2] on each iteration.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

can we use a for each loop, instead of for loop and over come the problem? , because i remember generating so many falt file within the for each loop at same execution time.

can we use a for each loop, instead of for loop and over come the problem? , because i remember generating so many falt file within the for each loop at same execution time. Try it and see. I don't think it will make a difference. The loop iterations are still sequential so you'll get the same problem.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

is there an expression for the time-stamp with out using the container time? because my manger doesnt want any script componenet, and on top of it i dont know C# to write a code in it. suggest please.