Sunday, February 10, 2013 8:27 PM
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
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
- Edited by Fox_00 Sunday, February 10, 2013 8:31 PM
Sunday, February 10, 2013 9:42 PMModerator
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
- Edited by SSISJoostMicrosoft Community Contributor, Moderator Sunday, February 10, 2013 9:44 PM
- Marked As Answer by Fox_00 Sunday, February 10, 2013 9:51 PM
Sunday, February 10, 2013 10:01 PM
Great!, exactly what I was after.