Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Connect to previous months table

Answered Connect to previous months table

  • Sunday, February 10, 2013 8:27 PM
     
     
    Sign In to Vote
    0

    Hi Folks

    I have a database that contains monthly tables that contain sales data 

    Each month I want to extract all the sales data from the previous months table.  I.e  on the 1st of March 2013  I want to  schedule a SSIS package to run to load February data into another database. On April the 1st I want to load March data and so on. 

    I.e the tables in the database have a name structure as follows 

    • Sales_2012_11
    • Sales_2012_12
    • Sales_2013_01
    • Sales_2013_02
    • Sales_2013_03

    What is the best way to connect to the previous months sales table? Because this needs to be scheduled package, I want to avoid having to update the table name in the select statement in a source connection manager. 

    Any suggestions welcome

    Regards

    Stevo



    • Edited by Fox_00 Sunday, February 10, 2013 8:31 PM
    •  
     

All Replies

  • Sunday, February 10, 2013 9:42 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    If the data is in different tables then you could make the source table (or the complete source query) dynamic.

    Create an SSIS variable type string and add an expression. Something like:

    "Sales_" +  (DT_WSTR,4)DATEPART("yyyy", DATEADD("mm", -1, GETDATE())) + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("mm", DATEADD("mm", -1, GETDATE())), 2)


    Now you can use this variable in the OLE DB Source

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Sunday, February 10, 2013 10:01 PM
     
     
    Sign In to Vote
    0

    Great!, exactly what I was after.

    Regards

    Steve