SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running package in a loop


Running package in a loop

Author
Message
SQL_Surfer
SQL_Surfer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 1098
I have a pacakge that pulls the data from the table for a month. It runs on the first of the month and it pulls the previous month of data.

Requirement is to generate files for June 2011 to present but broken them weekly based on the transaction date. How can I run it in a loop per week for that time period?
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10327 Visits: 7891
[sarcasm]Simply set the date bucket parameter to weekly and the range filter from June 2011 to today.[/sarcasm]

Sorry, feeling a bit Hehew00t

Truly...

Create a dates table (with weeks in it)
Add a data flow to grab the weeks from the dates table for the required range and feed that into a package variable (recordset destination)
use a foreach loop to loop through the weeks in the variable
feed the start and end dates from the week into your extract routine (where clause)
output to file (use expression to build file name using week number from loop)


If I have assumed too much knowledge and Google doesn't help , then ask questions by all means

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • SQL_Surfer
    SQL_Surfer
    SSCommitted
    SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

    Group: General Forum Members
    Points: 1598 Visits: 1098
    Thanks. Another question. I have a for each container that reads dates from the table. Inside the container, I've execut SQL task. I did the parameter mapping and seems like I can't access date variable in execute SQL task. Getting syntax error or access violation error. Any idea?
    Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

    Group: General Forum Members
    Points: 50289 Visits: 21151
    mister.magoo (2/20/2013)
    [sarcasm]Simply set the date bucket parameter to weekly and the range filter from June 2011 to today.[/sarcasm]

    Sorry, feeling a bit Hehew00t

    Truly...

    Create a dates table (with weeks in it)
    Add a data flow to grab the weeks from the dates table for the required range and feed that into a package variable (recordset destination)
    use a foreach loop to loop through the weeks in the variable
    feed the start and end dates from the week into your extract routine (where clause)
    output to file (use expression to build file name using week number from loop)


    If I have assumed too much knowledge and Google doesn't help , then ask questions by all means


    Alternatively, add a Conditional Split in the data flow and split the data into four streams - to four separate files.


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10327 Visits: 7891
    Phil Parkin (2/21/2013)

    Alternatively, add a Conditional Split in the data flow and split the data into four streams - to four separate files.


    I like this idea, but only if every month has four weeks in it ;-)

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10327 Visits: 7891
    SQL_Surfer (2/20/2013)
    Thanks. Another question. I have a for each container that reads dates from the table. Inside the container, I've execut SQL task. I did the parameter mapping and seems like I can't access date variable in execute SQL task. Getting syntax error or access violation error. Any idea?


    Can you explain how you are trying to use the variable?

    I was thinking that your existing routine would have a date range that it pulled and you would be replacing an existing set of variables with those from the loop (or calling your existing package from a new one and passing in the variables values to existing variables in the original package)...

    Are you calling a SP or just using a query ?

    More info please?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

    Group: General Forum Members
    Points: 50289 Visits: 21151
    mister.magoo (2/21/2013)
    Phil Parkin (2/21/2013)

    Alternatively, add a Conditional Split in the data flow and split the data into four streams - to four separate files.


    I like this idea, but only if every month has four weeks in it ;-)


    Well, I did give that some thought.

    And there has to be a prior step to set up variables containing the 'break dates' for the month too.

    But thought it worth a mention nonetheless.


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    SQL_Surfer
    SQL_Surfer
    SSCommitted
    SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

    Group: General Forum Members
    Points: 1598 Visits: 1098
    I have a table which stores date per week. I am passing those dates to my inline statement (in execute sql statement).
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10327 Visits: 7891
    SQL_Surfer (2/21/2013)
    I have a table which stores date per week. I am passing those dates to my inline statement (in execute sql statement).


    How? Show us please :-D

    What error are you getting? Show us the exact error please :-D

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • SQL_Surfer
    SQL_Surfer
    SSCommitted
    SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

    Group: General Forum Members
    Points: 1598 Visits: 1098
    It worked after converted inline statments to stord proc. thanks for your help.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search