dts
-
Hi:
I want to create dts package like this. I want to collect weekly data from one server and load into other server every week. The problem is they want to load into a different table name every week.
for example the source name is abc
they want to load like this.
abcmar202006 for first week
abcmar272006 for next week and they want to make it as a dts routine.
any suggestion helps me a lot
Thanks,
Prema
-
There are various ways of doing this. One of which is as follows
1. create 2 SQL connections, one to each server
2. create a "data transformation task between the 2 servers
3. use an SQL Task to construct the table depending on your settings for week,day month etc. You could use dynamic sql for this. Then store the tablename in a global variable.
Example:
DECLARE @TableName VARCHAR(100)
SET @TableName = "detemine the table name here depending on the date etc"
DECLARE @StrSQL VARCHAR(1000)
SET @StrSQL = 'CREATE TABLE ' + @TableName + 'set the column names etc here'
EXEC (@StrSQL) --> this creates the table
SELECT @TableName --> this returns the table name and can be stored as a global variable (output parameter for the SQL Task)
4. use the "Set Dynamic Names" task to change the destination table of the "Data Transformation" task
5. type your select statement in the "data transformation" task
6. step through the rest of the tabs
-
would you please explain how to set dynamic names task in sql server 2000
Thanks
-
Read BOL first...if you have anymore question after reading BOL, just ask.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply