SSIS Package, execute every 13 hours for previous 12 hours of data
I have a SSIS Package and I need to schedule it to run at 1:00 AM and 1:00 PM every day.
At 1:00 AM it should pass parameters of the previous day for e.g. 15 Nov 2018 12:00 PM as @StartDate & 15 Nov 2018 11:59 PM as @EndDate
At 1:00 PM it should pass parameters of the current day for e.g. 15 Nov 12:00 AM as @StartDate & 15 Nov 11:59 AM as @EndDate
A stored procedure is called through an OLE DB Source Editor task in Data Flow.
Does anyone have any suggestions that How I could achieve this noting the fact that if the job fails at 1:00 PM & it should re-run at 2:00 PM, it should still pass the same parameters.
sql-server tsql ssis dataflow asked Nov 16 '18 at 9:43
PhilipPhilip
769519
add a comment |
I have a SSIS Package and I need to schedule it to run at 1:00 AM and 1:00 PM every day.
At 1:00 AM it should pass parameters of the previous day for e.g. 15 Nov 2018 12:00 PM as @StartDate & 15 Nov 2018 11:59 PM as @EndDate
At 1:00 PM it should pass parameters of the current day for e.g. 15 Nov 12:00 AM as @StartDate & 15 Nov 11:59 AM as @EndDate
A stored procedure is called through an OLE DB Source Editor task in Data Flow.
Does anyone have any suggestions that How I could achieve this noting the fact that if the job fails at 1:00 PM & it should re-run at 2:00 PM, it should still pass the same parameters.
sql-server tsql ssis dataflow asked Nov 16 '18 at 9:43
PhilipPhilip
769519
You might want to rethink your StartDate and EndDate windows.15 Nov 12:00 PMis one minute AFTER15 Nov 11:59 AM. And at1:00 PM, if you run for the "current day", you'll be trying to look at 11 hours that haven't happened yet (your EndDate of15 Nov 11:59 PMis 10:59 after your run time).
– digital.aaron
Nov 16 '18 at 16:52
If "today" is15 Nov 2018, then at 1:00am, you should probably set@StartDate = '14 Nov 2018 12:00 PM'and@EndDate = '14 Nov 2018 11:59 PM'. Then at 1:00pm you would then set@StartDate = '15 Nov 2018 12:00 AM'and@EndDate = '15 Nov 2018 11:59 AM'
– digital.aaron
Nov 16 '18 at 16:58
Yes quite right @digital.aaron. Cheers.
– Philip
Nov 17 '18 at 0:55
If you want a job to always pick up from the correct spot then you need t o record this in a table and allow the job to use that table next time it runs. Then you don't have to hard code dates and times anywhere. You schedule it whenever you want and however frequently you want, and it just works.
– Nick.McDermaid
Nov 17 '18 at 3:23
Exactly @Nick.McDermaid, good point, if you want to post this as an answer I will accept.
– Philip
Nov 18 '18 at 0:50
add a comment |
I have a SSIS Package and I need to schedule it to run at 1:00 AM and 1:00 PM every day.
At 1:00 AM it should pass parameters of the previous day for e.g. 15 Nov 2018 12:00 PM as @StartDate & 15 Nov 2018 11:59 PM as @EndDate
At 1:00 PM it should pass parameters of the current day for e.g. 15 Nov 12:00 AM as @StartDate & 15 Nov 11:59 AM as @EndDate
A stored procedure is called through an OLE DB Source Editor task in Data Flow.
Does anyone have any suggestions that How I could achieve this noting the fact that if the job fails at 1:00 PM & it should re-run at 2:00 PM, it should still pass the same parameters.
sql-server tsql ssis dataflow asked Nov 16 '18 at 9:43
PhilipPhilip
769519
I have a SSIS Package and I need to schedule it to run at 1:00 AM and 1:00 PM every day.
At 1:00 AM it should pass parameters of the previous day for e.g. 15 Nov 2018 12:00 PM as @StartDate & 15 Nov 2018 11:59 PM as @EndDate
At 1:00 PM it should pass parameters of the current day for e.g. 15 Nov 12:00 AM as @StartDate & 15 Nov 11:59 AM as @EndDate
A stored procedure is called through an OLE DB Source Editor task in Data Flow.
Does anyone have any suggestions that How I could achieve this noting the fact that if the job fails at 1:00 PM & it should re-run at 2:00 PM, it should still pass the same parameters.
sql-server tsql ssis dataflow sql-server tsql ssis dataflow asked Nov 16 '18 at 9:43
PhilipPhilip
769519
asked Nov 16 '18 at 9:43
PhilipPhilip
769519
edited Nov 17 '18 at 0:55
Philip
asked Nov 16 '18 at 9:43
PhilipPhilip
769519
asked Nov 16 '18 at 9:43
PhilipPhilip
769519
asked Nov 16 '18 at 9:43
PhilipPhilip
769519
769519
You might want to rethink your StartDate and EndDate windows.15 Nov 12:00 PMis one minute AFTER15 Nov 11:59 AM. And at1:00 PM, if you run for the "current day", you'll be trying to look at 11 hours that haven't happened yet (your EndDate of15 Nov 11:59 PMis 10:59 after your run time).
– digital.aaron
Nov 16 '18 at 16:52
If "today" is15 Nov 2018, then at 1:00am, you should probably set@StartDate = '14 Nov 2018 12:00 PM'and@EndDate = '14 Nov 2018 11:59 PM'. Then at 1:00pm you would then set@StartDate = '15 Nov 2018 12:00 AM'and@EndDate = '15 Nov 2018 11:59 AM'
– digital.aaron
Nov 16 '18 at 16:58
Yes quite right @digital.aaron. Cheers.
– Philip
Nov 17 '18 at 0:55
If you want a job to always pick up from the correct spot then you need t o record this in a table and allow the job to use that table next time it runs. Then you don't have to hard code dates and times anywhere. You schedule it whenever you want and however frequently you want, and it just works.
– Nick.McDermaid
Nov 17 '18 at 3:23
Exactly @Nick.McDermaid, good point, if you want to post this as an answer I will accept.
– Philip
Nov 18 '18 at 0:50
add a comment |
You might want to rethink your StartDate and EndDate windows.15 Nov 12:00 PMis one minute AFTER15 Nov 11:59 AM. And at1:00 PM, if you run for the "current day", you'll be trying to look at 11 hours that haven't happened yet (your EndDate of15 Nov 11:59 PMis 10:59 after your run time).
– digital.aaron
Nov 16 '18 at 16:52
If "today" is15 Nov 2018, then at 1:00am, you should probably set@StartDate = '14 Nov 2018 12:00 PM'and@EndDate = '14 Nov 2018 11:59 PM'. Then at 1:00pm you would then set@StartDate = '15 Nov 2018 12:00 AM'and@EndDate = '15 Nov 2018 11:59 AM'
– digital.aaron
Nov 16 '18 at 16:58
Yes quite right @digital.aaron. Cheers.
– Philip
Nov 17 '18 at 0:55
If you want a job to always pick up from the correct spot then you need t o record this in a table and allow the job to use that table next time it runs. Then you don't have to hard code dates and times anywhere. You schedule it whenever you want and however frequently you want, and it just works.
– Nick.McDermaid
Nov 17 '18 at 3:23
Exactly @Nick.McDermaid, good point, if you want to post this as an answer I will accept.
– Philip
Nov 18 '18 at 0:50
You might want to rethink your StartDate and EndDate windows.
15 Nov 12:00 PM is one minute AFTER 15 Nov 11:59 AM. And at 1:00 PM, if you run for the "current day", you'll be trying to look at 11 hours that haven't happened yet (your EndDate of 15 Nov 11:59 PM is 10:59 after your run time).– digital.aaron
Nov 16 '18 at 16:52
You might want to rethink your StartDate and EndDate windows.
15 Nov 12:00 PM is one minute AFTER 15 Nov 11:59 AM. And at 1:00 PM, if you run for the "current day", you'll be trying to look at 11 hours that haven't happened yet (your EndDate of 15 Nov 11:59 PM is 10:59 after your run time).– digital.aaron
Nov 16 '18 at 16:52
If "today" is
15 Nov 2018, then at 1:00am, you should probably set @StartDate = '14 Nov 2018 12:00 PM' and @EndDate = '14 Nov 2018 11:59 PM'. Then at 1:00pm you would then set @StartDate = '15 Nov 2018 12:00 AM' and @EndDate = '15 Nov 2018 11:59 AM'– digital.aaron
Nov 16 '18 at 16:58
If "today" is
15 Nov 2018, then at 1:00am, you should probably set @StartDate = '14 Nov 2018 12:00 PM' and @EndDate = '14 Nov 2018 11:59 PM'. Then at 1:00pm you would then set @StartDate = '15 Nov 2018 12:00 AM' and @EndDate = '15 Nov 2018 11:59 AM'– digital.aaron
Nov 16 '18 at 16:58
Yes quite right @digital.aaron. Cheers.
– Philip
Nov 17 '18 at 0:55
Yes quite right @digital.aaron. Cheers.
– Philip
Nov 17 '18 at 0:55
If you want a job to always pick up from the correct spot then you need t o record this in a table and allow the job to use that table next time it runs. Then you don't have to hard code dates and times anywhere. You schedule it whenever you want and however frequently you want, and it just works.
– Nick.McDermaid
Nov 17 '18 at 3:23
If you want a job to always pick up from the correct spot then you need t o record this in a table and allow the job to use that table next time it runs. Then you don't have to hard code dates and times anywhere. You schedule it whenever you want and however frequently you want, and it just works.
– Nick.McDermaid
Nov 17 '18 at 3:23
Exactly @Nick.McDermaid, good point, if you want to post this as an answer I will accept.
– Philip
Nov 18 '18 at 0:50
Exactly @Nick.McDermaid, good point, if you want to post this as an answer I will accept.
– Philip
Nov 18 '18 at 0:50
add a comment |
1 Answer
1
active
oldest
votes
Here are the steps to pass the date parameters to OLEDB task.
- Have start date & end date parameters in (I suppose you must be having).
- Assign the dates values to both parameters using SQL task.
- Pass these parameters to OLDEB stored procedure to data flow task.
For scheduling:
Create two different jobs for each run, one for 1 AM & another for 2 AM of the job.
For second job run, implement the following steps.
Create a table which log the job execution status with date and time. That table has insertion after each run of the job.
When second job schedule execute first check log table in first step and check the last run status of the job.
If it was successful exit else go to next step.
Hope this will help.
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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%2f53335153%2fssis-package-execute-every-13-hours-for-previous-12-hours-of-data%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here are the steps to pass the date parameters to OLEDB task.
- Have start date & end date parameters in (I suppose you must be having).
- Assign the dates values to both parameters using SQL task.
- Pass these parameters to OLDEB stored procedure to data flow task.
For scheduling:
Create two different jobs for each run, one for 1 AM & another for 2 AM of the job.
For second job run, implement the following steps.
Create a table which log the job execution status with date and time. That table has insertion after each run of the job.
When second job schedule execute first check log table in first step and check the last run status of the job.
If it was successful exit else go to next step.
Hope this will help.
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
add a comment |
Here are the steps to pass the date parameters to OLEDB task.
- Have start date & end date parameters in (I suppose you must be having).
- Assign the dates values to both parameters using SQL task.
- Pass these parameters to OLDEB stored procedure to data flow task.
For scheduling:
Create two different jobs for each run, one for 1 AM & another for 2 AM of the job.
For second job run, implement the following steps.
Create a table which log the job execution status with date and time. That table has insertion after each run of the job.
When second job schedule execute first check log table in first step and check the last run status of the job.
If it was successful exit else go to next step.
Hope this will help.
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
add a comment |
Here are the steps to pass the date parameters to OLEDB task.
- Have start date & end date parameters in (I suppose you must be having).
- Assign the dates values to both parameters using SQL task.
- Pass these parameters to OLDEB stored procedure to data flow task.
For scheduling:
Create two different jobs for each run, one for 1 AM & another for 2 AM of the job.
For second job run, implement the following steps.
Create a table which log the job execution status with date and time. That table has insertion after each run of the job.
When second job schedule execute first check log table in first step and check the last run status of the job.
If it was successful exit else go to next step.
Hope this will help.
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
Here are the steps to pass the date parameters to OLEDB task.
- Have start date & end date parameters in (I suppose you must be having).
- Assign the dates values to both parameters using SQL task.
- Pass these parameters to OLDEB stored procedure to data flow task.
For scheduling:
Create two different jobs for each run, one for 1 AM & another for 2 AM of the job.
For second job run, implement the following steps.
Create a table which log the job execution status with date and time. That table has insertion after each run of the job.
When second job schedule execute first check log table in first step and check the last run status of the job.
If it was successful exit else go to next step.
Hope this will help.
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
edited Nov 16 '18 at 18:49
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
answered Nov 16 '18 at 10:01
Rahul NeekhraRahul Neekhra
6001627
6001627
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
add a comment |
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
Hi Rahul, thanks for responding, but it doesn't really make sense and needs more detail. The jobs are 1am, and 1pm, not 1am and 2am.
– Philip
Nov 17 '18 at 0:54
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f53335153%2fssis-package-execute-every-13-hours-for-previous-12-hours-of-data%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
You might want to rethink your StartDate and EndDate windows.
15 Nov 12:00 PMis one minute AFTER15 Nov 11:59 AM. And at1:00 PM, if you run for the "current day", you'll be trying to look at 11 hours that haven't happened yet (your EndDate of15 Nov 11:59 PMis 10:59 after your run time).– digital.aaron
Nov 16 '18 at 16:52
If "today" is
15 Nov 2018, then at 1:00am, you should probably set@StartDate = '14 Nov 2018 12:00 PM'and@EndDate = '14 Nov 2018 11:59 PM'. Then at 1:00pm you would then set@StartDate = '15 Nov 2018 12:00 AM'and@EndDate = '15 Nov 2018 11:59 AM'– digital.aaron
Nov 16 '18 at 16:58
Yes quite right @digital.aaron. Cheers.
– Philip
Nov 17 '18 at 0:55
If you want a job to always pick up from the correct spot then you need t o record this in a table and allow the job to use that table next time it runs. Then you don't have to hard code dates and times anywhere. You schedule it whenever you want and however frequently you want, and it just works.
– Nick.McDermaid
Nov 17 '18 at 3:23
Exactly @Nick.McDermaid, good point, if you want to post this as an answer I will accept.
– Philip
Nov 18 '18 at 0:50