 | |
SSIS Variables
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
RE: SSIS Variables -
10-29-2008
, 07:43 AM
|
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] "richard.hunt" wrote: 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 |
#12
| |||
| |||
|
|
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] "richard.hunt" wrote: 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 |
#13
| |||
| |||
|
|
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] "richard.hunt" wrote: 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 |
#14
| |||
| |||
|
|
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] "richard.hunt" wrote: 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 |
#15
| |||
| |||
|
|
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] "richard.hunt" wrote: 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 |
#16
| |||
| |||
|
|
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] "richard.hunt" wrote: 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 |
#17
| |||
| |||
|
|
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] "richard.hunt" wrote: 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 |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.