Dynamically using the Table name

Question

Dynamically using the Table name

Ansari Mohamed Asif

SSC Veteran

Points: 246
More actions
June 13, 2005 at 11:05 pm

#61144

Hello,
I want to create a DTS package where the data from one table will be copied to other table.
The data has to be copied from lots of source table to a destination table.
I don't want to modify DTS package each time for a new source table instead I want to assign the table name dynamically at runtime.
How can I do it??
Any help plz.
Thnx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Create a DTS package that copies one table. Then save this package as a VB file. Take a lokk at how the transformation and column mapping are done. You'll need to dynamically replicate all the property creation and setting in an ActiveXScript task that executes before the datapump.

Been there, tried that, ran away screaming and settled on nice easy simple straight forward packages.

--------------------
Colt 45 - the original point and click interface

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 2

Another way to accomplish is to use a dynamic properites task pointing the source table name to a value in an ini file. You only need to change the value in an ini file in order to run the package. See this really good article that got me started. You can also set database name and target table names at runtime.

http://www.databasejournal.com/features/mssql/article.php/3073161

------------------------------
The Users are always right - when I'm not wrong!

Ansari Mohamed Asif SSC Veteran Points: 246 More actions · Answer 3

Ansari Mohamed Asif

SSC Veteran

Points: 246

June 14, 2005 at 4:42 am

#565213

Thanks very much.

It helped me.

philcart SSC-Forever Points: 47794 More actions · Answer 4

Yes this works well for multiple tables where the structure is the same. If you try the same approach with tables that have a different column structure without modifying the column mappings, then the package won't work.

--------------------
Colt 45 - the original point and click interface

Ansari Mohamed Asif SSC Veteran Points: 246 More actions · Answer 5

Hello Colleagues,

The package works fine with INI file.

But still I have to change the table name manually in INI file.

Actually name of the tables(One table per 2 days of a month) which I wanna copy through DTS is stored in other table say table_names.

At runtime the package should select the name of the table from table_names table corresponding to the current date and then start transferring the data from selected table across machines.

Any help will be appreciated.

Thanks in advance.

Bye.

urvashi SSC Enthusiast Points: 115 More actions · Answer 6

Hi,

You have to create a dynamic properties task in which you need to go Tasks and select the task for which you need source table name.....

select the source object name and then set... Select the source as query and the sql statement to get the table name and then when you right click the task for which you need the source table and go to workflow properties, add a new precedence there for which the source step would be the newly created dynamic task.

Hope this helps

Urvashi

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 7

Hi

You can try one more way...

Create an 'Execute SQL Task' for getting Source Table name

----------------------------------------------------------

Ex:- Select Source_Table from Table_Names

Store the out put in a Global Variable....

Create an 'ActiveX Script Task' for Assigning 'Global Variable' to the 'DTSDataPumpTask' Property

----------------------------------------------------------------------------------------------

Ex:-

Dim objPackage,objTask

Set objPackage = DTSGlobalVariables.Parent

Set objTask = objPackage.Tasks(cstr("DTSTask_DTSDataPumpTask_1")).CustomTask

objTask.SourceObjectName = DTSGlobalVariables("Source_Table").Value

Hope this will help you...

Ansari Mohamed Asif SSC Veteran Points: 246 More actions · Answer 8

Hello Colleagues,

Thanks very much for your interest and suggestions.

I have tried the suggestion given by Urvashi,it is working fine.

Regards.