SQL Server Performance

Using different destination tables for every month

Discussion in 'SQL Server DTS-Related Questions' started by emamuthu, Jan 16, 2006.

  1. emamuthu New Member

    Hi,
    Is there anyway around to use diffenet destination table every months without opening & changing the table name in the dts package.

    Since we load around 40 millons rows every month in the single table...we need to create new table every month for performance issue.

    I was doing manually going inside the package & changing the destination table at the end of month load. I found out we can't use view name as desination name.

    I know we can do it using activex script , but don't want to do that way since it will slow down the load performance.

    Any advice.

    Thanks
    Muthu
  2. satya Moderator

    You can use Global variables to achieve the task, refer to books online andhttp://www.sqldts.com for relevant information.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. emamuthu New Member

    Is there any example which shows that...i searched in the sqldts i was not able to find one..

    Thanks
    Muthu
  4. satya Moderator

  5. emamuthu New Member

    Thanks satya...

    This was vey helpful

    Thanks
    Muthu
  6. ji06 New Member

    Hi all,

    You could use this approach: (this is valid as long as you use SQL 2000)

    Main tables (where you will insert productive data)
    Table_January
    Table_February
    ..

    One parameter table to specific which table is used when the script runs, it means

    2006/01/21 Table_January
    2006/02/15 Table_February
    ..

    In your DTS you have one Transformation Task, so you only have to change some properties of this task to use the corresponding destination table depend on which day runs, to do that:

    - Create a DynamicPropertiesTask and set the destination object name of your transformation task getting from your parameter table. (This task it has several option to set parameters. ini file, tables, etc..)

    So if you dts runs on 2006/02/15, the dynamic task will look up this value in your parameter table (just create the SQL statement to query this table) and it will get and set Table_February as destination object. (Have a look how this task works in the SQL Book Online)


    In this way, you can configure your process as you want and you dont need to change your dts every month.

Share This Page