SQL Server Performance Forum – Threads Archive

Using different destination tables for every month

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

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.

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

http://www.nigelrivett.net/
http://www.sqldts.com/default.aspx?252
http://www.sqldts.com/default.aspx?237
http://www.databasejournal.com/features/mssql/article.php/3073161 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.

Thanks satya… This was vey helpful Thanks
Muthu

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.

]]>