Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running package in a loop Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 3:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:56 PM
Points: 332, Visits: 868
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?
Post #1422341
Posted Wednesday, February 20, 2013 4:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 1,805, Visits: 5,867
[sarcasm]Simply set the date bucket parameter to weekly and the range filter from June 2011 to today.[/sarcasm]

Sorry, feeling a bit

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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1422357
    Posted Wednesday, February 20, 2013 10:04 PM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Thursday, October 16, 2014 12:56 PM
    Points: 332, Visits: 868
    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?
    Post #1422393
    Posted Thursday, February 21, 2013 2:09 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 5:15 PM
    Points: 5,245, Visits: 12,163
    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

    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.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1422443
    Posted Thursday, February 21, 2013 2:16 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:24 PM
    Points: 1,805, Visits: 5,867
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1422446
    Posted Thursday, February 21, 2013 2:19 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:24 PM
    Points: 1,805, Visits: 5,867
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1422448
    Posted Thursday, February 21, 2013 2:20 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 5:15 PM
    Points: 5,245, Visits: 12,163
    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.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1422449
    Posted Thursday, February 21, 2013 7:20 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Thursday, October 16, 2014 12:56 PM
    Points: 332, Visits: 868
    I have a table which stores date per week. I am passing those dates to my inline statement (in execute sql statement).
    Post #1422575
    Posted Thursday, February 21, 2013 7:33 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:24 PM
    Points: 1,805, Visits: 5,867
    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

    What error are you getting? Show us the exact error please


    MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1422584
    Posted Thursday, February 21, 2013 1:31 PM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Thursday, October 16, 2014 12:56 PM
    Points: 332, Visits: 868
    It worked after converted inline statments to stord proc. thanks for your help.
    Post #1422778
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse