Hi:



I want to create a dts package and schedule to run every week like
this:



The Server name A. It has a table abc. I want to collect the one week
data from table abc and

has to load to server B with table name abc060322. Next week I have to
load the data with table name abc060329.



I wrote the query to create dynamic table every week: The query is



DECLARE @TableName VARCHAR(100)

SET @TableName = convert(varchar(11),getdate(),12)

--print @tablename



/*DECLARE @pos INT

SELECT @pos = CHARINDEX('/',@tablename)



WHILE @pos > 0

BEGIN

SELECT @tablename = SUBSTRING(@tablename,1,@pos - 1) +

SUBSTRING(@tablename,@pos + 1,50-@pos )

SELECT @pos = CHARINDEX('/',@tablename)

END

-- PRINT 'Name without DASHES is :'

-- PRINT @tablename*/

SELECT @tablename = 'GISLOADER.ACC' + @tablename

DECLARE @StrSQL VARCHAR(2000)

SET @StrSQL = ('CREATE TABLE ' + @tablename + '([CV_INSERT_IDENTITY] int
null,

     [RecordID] [int] IDENTITY (1, 1) NOT NULL ,

     [_TimeStamp] [timestamp] NULL ,

     [ACCSUnit] [varchar](20)NULL ,

     [ACCSDir] [varchar](20) NULL ,

     [ACCLName] [varchar](20)NULL ,

     [ACCSZip] [varchar](20) NULL ,

     [ACCSState] [varchar](20)NULL ,

     [ACCSCity] [varchar](20)NULL ,

     [ACCSType] [varchar](20)NULL ,

     [ACCPhone] [varchar](20)NULL ,

     [ACCSName] [varchar](20)NULL ,

     [ACCSNum] [varchar](20)NULL ,

     [ACCFName] [varchar](20)NULL ,

     [ACCCompType] [varchar](4)NULL ,

     [ACCEmail] [varchar](40)NULL ,

     [ACCTimeResolved] [datetime]NULL ,

     [ACCTimeofCall] [datetime]NULL ,

     [ACCDateResolved] [datetime]NULL ,

     [ACCDateofCall] [datetime]NULL ,

     [ACCOfficer][varchar](4)NULL ,

     [ACCFine] [bit] NULL ,

     [ACCViolation][varchar](4)NULL ,

     [ACCFineAmt][money]NULL,

     [ACCPet] [int] NULL ,

     [ACCResStatus] [varchar] (4) NULL ,

     [ACLSZip] [varchar](20) NULL ,

     [ACLSCity] [varchar](20)NULL ,

     [ACLSType] [varchar](20)NULL ,

     [ACLSName] [varchar](20)NULL ,

     [ACLSNum] [varchar](20)NULL ,

     [ACLSUnit] [varchar](20)NULL ,

     [ACLSState] [varchar](20)NULL ,

     [ACLSDir] [varchar](20)NULL ,

     [ParcelID] [int] NULL ,

     [CitationNo] [varchar](20)NULL ,

     [PickUpCat] [smallint]NULL ,

     [PickUpDog] [smallint]NULL ,

     [PickUpOther] [smallint]NULL ,

     [Contact] [bit] NULL )')

EXEC (@StrSQL) --> this creates the table



select @tablename.



I wrote the query for selecting the weekly data too:



select [CV_INSERT_IDENTITY] ,

     [RecordID],

     [_TimeStamp],

     [ACCSUnit] ,

     [ACCSDir] ,

     [ACCLName],

     [ACCSZip] ,

     [ACCSState] ,

     [ACCSCity] ,

     [ACCSType],

     [ACCPhone] ,

     [ACCSName],

     [ACCSNum] ,

     [ACCFName],

     [ACCCompType],

     [ACCEmail],

     [ACCTimeResolved],

     [ACCTimeofCall] ,

     [ACCDateResolved] ,

     [ACCDateofCall] ,

     [ACCOfficer] ,

     [ACCFine] ,

     [ACCViolation],

     [ACCFineAmt],

     [ACCPet] ,

     [ACCResStatus],

     [ACLSZip] ,

     [ACLSCity],

     [ACLSType],

     [ACLSName],

     [ACLSNum] ,

     [ACLSUnit] ,

     [ACLSState],

     [ACLSDir] ,

     [ParcelID] ,

     [CitationNo],

     [PickUpCat] ,

     [PickUpDog] ,

     [PickUpOther] ,

     [Contact]

from animalcontrolcalls

where

Accdateofcall >=( getdate() - 7) and ACCdateofcall <= '2500-01-01'



The problem is , I don't how to map the transformation (to pick dynamic
table every week)



The table is going to have the same structure, table name only differs.

I know this can be done thru dynamic properties task. But i don't know how to do it. I am newbie to sql server. Would you please explain step by step thru snapshots


Thank you in advance,



Prema

Prema,

Setup your SQL task that creates the table. Then on the Output Parameter pass the tablename to a global variable. Then setup your Dynamic Properties task (so that it executes after your SQL task and before your Pump task) tell it use the global variable as the source.

An article I wrote has some screenshots of Dynamic properties even though it is not strictly about your problem:
http://www.mutuallybeneficial.com/in...s_portable.htm

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com