December 31, 2007 at 1:10 am
Hi,
I have a source table which contains monthly data. Every month the name of the table changes to include the new month as follows:-
November -> schemaname_table_name_1107
December -> schemaname_table_name_1207
January -> schemaname_table_name_0108
etc.....
I have a dataflow task that contains a source, data conversion and destination. I was wondering how I could change the source table name at runtime so that I don't have to manually change the name everytime I run the package.
Just to be clear: the table structure is fixed, the first portion of the table name is fixed and the source database is fixed.
I was thinking about variables however, I really don't know where to begin and how to go about it.
Cheers!
December 31, 2007 at 1:16 am
use "sp_rename" see BOL for more information.
December 31, 2007 at 1:30 am
sp_rename only works with SQL Server and my source is an oracle databse and I need to build an expression with the source to change table name everytime so I don't think it will work
December 31, 2007 at 6:50 am
In the data flow task, create a string variable User::varCommand and set the following properties:
EvaluateAsExpression=True
Expression="SELECT " +
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()), 2) +
RIGHT((DT_WSTR, 4)(YEAR(GETDATE())), 2)
(Replace )
Now use an OLE DB Source to read the table and set the following properties:
AccessMode=SQL Command From Variable
SqlCommandVariable=User::varCommand
Be sure the table for the current month exists whenever you edit or run the package, otherwise the metadata will be lost.
Hope this helps
Peter
December 31, 2007 at 7:28 am
Expression="SELECT " +
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()), 2) +
RIGHT((DT_WSTR, 4)(YEAR(GETDATE())), 2)
(Replace)
I receive an error on the expression shown above. The Right function takes two parameters. One is the character expression which in the above code is shown by "0" - i think this should be the variable name???
December 31, 2007 at 9:24 am
I tried Peter's expression. It works correctly.
It sets the variable to: SELECT 1207
What is the error that BIDS is throwing?
Norman
DTS Package Search
December 31, 2007 at 9:35 am
Oops,
I inserted some placeholders in the expression surrounded by less- and greater then characters. Probably disappeared as invalid HTML-tags.
The expression should be something like
Expression="SELECT fields FROM table-prefix" +
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()), 2) +
RIGHT((DT_WSTR, 4)(YEAR(GETDATE())), 2)
(Replace "fields'' with the columns from your tables and "table-prefix" with the prefix of your tables).
The first RIGHT function takes two arguments:
1. "0" + (DT_WSTR, 2)MONTH(GETDATE()), the number of the month casted to a string with a leading zero added (for the months 1-9).
2. 2, telling the RIGHT function to take the last two character of the first argument
The second RIGHT function cuts off the last two digits from the current year casted to a string.
Given the current date, the expression evaluates to "SELECT fields FROM table-prefix1207"
Peter
January 1, 2008 at 10:04 pm
THANKS ALOT PETER! The expression works great!!! Appreciate it!
January 2, 2008 at 12:39 am
Hi again,
I managed to modify peter's expression to cater for the case when data for a given month is not available till the begining of the next month as follows:-
"select * from TABLE_NAME_" +
right((DT_WSTR,20)month(dateadd("mm",-1,getdate())),2) +
right((DT_WSTR,50)year(dateadd("mm",-1,getdate())),2)
this gives a result of:-
select * from table_name_1207
Thanks again Peter!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply