Running Different Data Flow Tasks Based on Time of Day

  • In SSIS 2008, I want to run a package once per hour (24 times a day). If the time that the package is running is before 5:00 p.m., I want to run Data Flow Task #1. But if the time is after 5:00 p.m. I want to run Data Flow Task #2. How can I pull this off? I was considering precedence constraints containing some kind of expression.

  • imani_technology (10/7/2010)


    In SSIS 2008, I want to run a package once per hour (24 times a day). If the time that the package is running is before 5:00 p.m., I want to run Data Flow Task #1. But if the time is after 5:00 p.m. I want to run Data Flow Task #2. How can I pull this off? I was considering precedence constraints containing some kind of expression.

    I would start with a task that assigns the time of day to a variable. Connect this task to both dataflows. Set both precedence constraints to use an expression. Set one expression to be true for the variable's value is less than 5pm and the other true if the variable's value is greater than 5pm. Actually, set one to <= or the other to >=, just in case the time is exactly 5pm.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks! Do I need a script task for this or some other kind of task?

  • imani_technology (10/7/2010)


    Thanks! Do I need a script task for this or some other kind of task?

    You have a few choices. You need to a task before the dataflows so that you can use the precedence constraints to determine which dataflow to run. You could an expression to assign the value to a variable then use any task you want as that first task.

    I would use a script task as that first task and have it assign the value to the variable.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I've created the Script Task and a variable, but I have no idea how to get the Script Task to assign the time of day to the variable.

  • imani_technology (10/7/2010)


    I've created the Script Task and a variable, but I have no idea how to get the Script Task to assign the time of day to the variable.

    Why can't people take the time to learn how to use a technology before going over their head into it????

    Do you know how to use Google, Bing, or any other search engine? Have you looked at Books on line?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Hint: Use DateTime Class or any DateTime Function of .net library in ST

    Alvin: Just a hint. Not much.

    Raunak J

  • Thanks for the hint. I ended up using Now().ToShortTimeString.

  • Just out of curiosity...why not create seperate package and schedule them as required?:cool:

    Raunak J

  • Hi sir

    i have a package in ssis

    that is how to change system.int32 datatype to system.int64

    which transformation i need apply

    now i am using oledb source,dataconversion,derived column,slowly changing dimension in my package

  • SamaReddy (10/12/2010)


    Hi sir

    i have a package in ssis

    that is how to change system.int32 datatype to system.int64

    which transformation i need apply

    now i am using oledb source,dataconversion,derived column,slowly changing dimension in my package

    So, you hijack a thread to ask a question that has nothing to do with the original question? Why don't you start a new thread?

    Also, the question isn't that clear. What are you trying to accomplish? (actually, there isn't a question. No question mark, no question)

    System.Int32 isn't a SSIS datatype, but a .NET datatype if I'm not mistaken. However, in your list of tasks used, I do not see a Script Task/Component.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Yes i solved my problem:hehe::hehe:

  • I think you are overcomplicating the solution by adding a script task. I think the easiest solution would be to create two separate jobs that run at different times or put the getdate/jobstartdate in a variable and use precedence constraints to determine which data flow to run.

    The script task/script components are wonderful options to expand the usability of SSIS, but I see lots of people using them when there is a much simpler solution that doesn't require any custom coding. A great benefit of a tool like SSIS is that another developer can look at a package and figure out what is going on in a relatively short amount of time versus going in and reading through lines of code. It is self documenting and self commenting in a lot of respects - I try not to take away from this strength as much as I can - if I can help it.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply