SSIS Variable Changing Time Component
up vote
1
down vote
favorite
I have a SSIS Variable named "DestinationDirectory" that has the following Expression:
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) GETDATE() ),1,19),":","")
It returns for example:
C:Finance Extract2018-11-10 125913
I then have a heap of other variables such as the one below named "DestinationFileAdjustment" that work off "DestinationDirectory". It has the following expression:
@[User::DestinationDirectory]
+ "\Adjustment_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())
+ ".csv"
What is supposed to happen is a file be created within that directory. The problem I'm experiencing is each time @DestinationDirectory is referred to, it is returning a different time value (e.g. C:Finance Extract2018-11-10 125914) and errors out because the directory does not exist.
Is there a way I can set that variable at run-time and it retain the same time value each and every time it is referred to?
sql-server ssis asked Nov 10 at 2:03
Philip
547418
add a comment |
up vote
1
down vote
favorite
I have a SSIS Variable named "DestinationDirectory" that has the following Expression:
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) GETDATE() ),1,19),":","")
It returns for example:
C:Finance Extract2018-11-10 125913
I then have a heap of other variables such as the one below named "DestinationFileAdjustment" that work off "DestinationDirectory". It has the following expression:
@[User::DestinationDirectory]
+ "\Adjustment_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())
+ ".csv"
What is supposed to happen is a file be created within that directory. The problem I'm experiencing is each time @DestinationDirectory is referred to, it is returning a different time value (e.g. C:Finance Extract2018-11-10 125914) and errors out because the directory does not exist.
Is there a way I can set that variable at run-time and it retain the same time value each and every time it is referred to?
sql-server ssis asked Nov 10 at 2:03
Philip
547418
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a SSIS Variable named "DestinationDirectory" that has the following Expression:
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) GETDATE() ),1,19),":","")
It returns for example:
C:Finance Extract2018-11-10 125913
I then have a heap of other variables such as the one below named "DestinationFileAdjustment" that work off "DestinationDirectory". It has the following expression:
@[User::DestinationDirectory]
+ "\Adjustment_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())
+ ".csv"
What is supposed to happen is a file be created within that directory. The problem I'm experiencing is each time @DestinationDirectory is referred to, it is returning a different time value (e.g. C:Finance Extract2018-11-10 125914) and errors out because the directory does not exist.
Is there a way I can set that variable at run-time and it retain the same time value each and every time it is referred to?
sql-server ssis asked Nov 10 at 2:03
Philip
547418
I have a SSIS Variable named "DestinationDirectory" that has the following Expression:
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) GETDATE() ),1,19),":","")
It returns for example:
C:Finance Extract2018-11-10 125913
I then have a heap of other variables such as the one below named "DestinationFileAdjustment" that work off "DestinationDirectory". It has the following expression:
@[User::DestinationDirectory]
+ "\Adjustment_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , GETDATE())
+ ".csv"
What is supposed to happen is a file be created within that directory. The problem I'm experiencing is each time @DestinationDirectory is referred to, it is returning a different time value (e.g. C:Finance Extract2018-11-10 125914) and errors out because the directory does not exist.
Is there a way I can set that variable at run-time and it retain the same time value each and every time it is referred to?
sql-server ssis sql-server ssis asked Nov 10 at 2:03
Philip
547418
asked Nov 10 at 2:03
Philip
547418
asked Nov 10 at 2:03
Philip
547418
asked Nov 10 at 2:03
Philip
547418
asked Nov 10 at 2:03
Philip
547418
547418
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
Since you're using GETDATE() in your expression, I'm guessing that you're using the time that the package begins in the "DestinationDirectory" variable? If so, using the @[System::StartTime] system variable for the expression in this variable (example below) will provide this time and be consistent throughout the execution of the package.
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) @[System::StartTime]),1,19),":","")
answered Nov 12 at 18:33
userfl89
1,01159
1
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
add a comment |
up vote
1
down vote
What I finished up doing to get around this was to have a Script Task and the following code within it:
Dts.Variables("User::DestinationDirectory").Value = Dts.Variables("User::SourceDirectory").Value.ToString + "\" + Replace(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ":", "")
That hard coded the value for the variable nicely.
answered Nov 11 at 3:14
Philip
547418
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Since you're using GETDATE() in your expression, I'm guessing that you're using the time that the package begins in the "DestinationDirectory" variable? If so, using the @[System::StartTime] system variable for the expression in this variable (example below) will provide this time and be consistent throughout the execution of the package.
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) @[System::StartTime]),1,19),":","")
answered Nov 12 at 18:33
userfl89
1,01159
1
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
add a comment |
up vote
1
down vote
accepted
Since you're using GETDATE() in your expression, I'm guessing that you're using the time that the package begins in the "DestinationDirectory" variable? If so, using the @[System::StartTime] system variable for the expression in this variable (example below) will provide this time and be consistent throughout the execution of the package.
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) @[System::StartTime]),1,19),":","")
answered Nov 12 at 18:33
userfl89
1,01159
1
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Since you're using GETDATE() in your expression, I'm guessing that you're using the time that the package begins in the "DestinationDirectory" variable? If so, using the @[System::StartTime] system variable for the expression in this variable (example below) will provide this time and be consistent throughout the execution of the package.
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) @[System::StartTime]),1,19),":","")
answered Nov 12 at 18:33
userfl89
1,01159
Since you're using GETDATE() in your expression, I'm guessing that you're using the time that the package begins in the "DestinationDirectory" variable? If so, using the @[System::StartTime] system variable for the expression in this variable (example below) will provide this time and be consistent throughout the execution of the package.
@[User::SourceDirectory] + "\" + REPLACE(SUBSTRING((DT_WSTR,35)((DT_DBTIMESTAMPOFFSET, 7) @[System::StartTime]),1,19),":","")
answered Nov 12 at 18:33
userfl89
1,01159
answered Nov 12 at 18:33
userfl89
1,01159
answered Nov 12 at 18:33
userfl89
1,01159
answered Nov 12 at 18:33
userfl89
1,01159
1,01159
1
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
add a comment |
1
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
1
1
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
Thank you for that, good piece of advice,.
– Philip
Nov 13 at 0:11
add a comment |
up vote
1
down vote
What I finished up doing to get around this was to have a Script Task and the following code within it:
Dts.Variables("User::DestinationDirectory").Value = Dts.Variables("User::SourceDirectory").Value.ToString + "\" + Replace(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ":", "")
That hard coded the value for the variable nicely.
answered Nov 11 at 3:14
Philip
547418
add a comment |
up vote
1
down vote
What I finished up doing to get around this was to have a Script Task and the following code within it:
Dts.Variables("User::DestinationDirectory").Value = Dts.Variables("User::SourceDirectory").Value.ToString + "\" + Replace(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ":", "")
That hard coded the value for the variable nicely.
answered Nov 11 at 3:14
Philip
547418
add a comment |
up vote
1
down vote
up vote
1
down vote
What I finished up doing to get around this was to have a Script Task and the following code within it:
Dts.Variables("User::DestinationDirectory").Value = Dts.Variables("User::SourceDirectory").Value.ToString + "\" + Replace(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ":", "")
That hard coded the value for the variable nicely.
answered Nov 11 at 3:14
Philip
547418
What I finished up doing to get around this was to have a Script Task and the following code within it:
Dts.Variables("User::DestinationDirectory").Value = Dts.Variables("User::SourceDirectory").Value.ToString + "\" + Replace(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ":", "")
That hard coded the value for the variable nicely.
answered Nov 11 at 3:14
Philip
547418
answered Nov 11 at 3:14
Philip
547418
answered Nov 11 at 3:14
Philip
547418
answered Nov 11 at 3:14
Philip
547418
547418
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53235413%2fssis-variable-changing-time-component%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
