SSIS Variables

[richard.hunt]

Ok,
So here is what I have. I have 2 different sql queries that need to go into
the same flat file. What I've done is created a dynamic variable which
calculates the date and time as such (SUBSTRING(REPLACE(REPLACE(REPLACE(
(DT_STR, 50, 1252) GETDATE(),"-","")," ",""),":",""),1,14) ) I then use this
to concatenate to another variable with the directory path to basically come
up with a file path for a new flat file. I then create 2 Flat file
connections with different columns but point to the same variable that stores
the connection information. This works. The issue is because the file is
date sensitive, when the package runs, depending on how long it takes to
write the data to the file, I end up with 2 different files because the time
has changed.
My question, how do I get the variable to calculate once during the entire
package?
I hope that I am clear in communicating my question.

Thank you

[Todd C]

Richard:

Try a Script Task at the beginning of the Control Flow. Have it populate the
Variable appropriately. (I usually use something like: YEAR(GetDate) * 10000
+ Month(GetDate) * 100 + Day(GetDate) which yields a numeric in the format of
YYYYMMDD.)

Then use the Expression Builder for your Flat File's Connection Manager's
Connection String.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by richard.hunt
Ok,
So here is what I have. I have 2 different sql queries that need to go into
the same flat file. What I've done is created a dynamic variable which
calculates the date and time as such (SUBSTRING(REPLACE(REPLACE(REPLACE(
(DT_STR, 50, 1252) GETDATE(),"-","")," ",""),":",""),1,14) ) I then use this
to concatenate to another variable with the directory path to basically come
up with a file path for a new flat file. I then create 2 Flat file
connections with different columns but point to the same variable that stores
the connection information. This works. The issue is because the file is
date sensitive, when the package runs, depending on how long it takes to
write the data to the file, I end up with 2 different files because the time
has changed.
My question, how do I get the variable to calculate once during the entire
package?
I hope that I am clear in communicating my question.
Thank you

[richard.hunt]

Thank you Todd
Yes, I did that last night and it worked. Thank you again for your time.

Richard Hunt

Post by Todd C
Try a Script Task at the beginning of the Control Flow. Have it populate the
Variable appropriately. (I usually use something like: YEAR(GetDate) * 10000
+ Month(GetDate) * 100 + Day(GetDate) which yields a numeric in the format of
YYYYMMDD.)
Then use the Expression Builder for your Flat File's Connection Manager's
Connection String.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by richard.hunt
Ok,
So here is what I have. I have 2 different sql queries that need to go into
the same flat file. What I've done is created a dynamic variable which
calculates the date and time as such (SUBSTRING(REPLACE(REPLACE(REPLACE(
(DT_STR, 50, 1252) GETDATE(),"-","")," ",""),":",""),1,14) ) I then use this
to concatenate to another variable with the directory path to basically come
up with a file path for a new flat file. I then create 2 Flat file
connections with different columns but point to the same variable that stores
the connection information. This works. The issue is because the file is
date sensitive, when the package runs, depending on how long it takes to
write the data to the file, I end up with 2 different files because the time
has changed.
My question, how do I get the variable to calculate once during the entire
package?
I hope that I am clear in communicating my question.
Thank you