asked on

can DTS perform dynamically???

Hi I'm new to Data Transformtion Service, Does DTS can performall all transformation and act like a program?

What I meant is that does it able to ask you for database name, create differnt table upon falling to different logical formula, etc. like Visual Basic. I know it's has ActiveX VB Script but it's only get data that are provide through the source and destination only, not able to twist the database. And does ActiveX Script able to execute SQL statement?

curtis591

You can change any portion of a dts package from com objects. You can call the program from Visual Basic, Foxpro whatever and you can access all the properties of the package. The following code changes the source database that the data is coming from before it executes the package.

dts_package = CreateObject("DTS.package")
dts_package.LoadFromSQLServer (server, user, password, , , , , alltrim(ppackage))

dts_package.Connections.Item(2).Catalog = database_name dts_package.connections.item(2).datasource = sever_name

dts_package.Execute

From my experience from using them I make the DTS Package do its thing with out changing any properties through activex script and I use my calling program to switch the databases and commands.

To execute sql in an activex script you have to use a adodb connections and recordsets. Just like visual basic.

ASTronus

ASKER

if I have a complex structure like creating dynamic tables base on other tables,joining different fields together and perform calculation, can it be done done with ActiveX Script? which one is faster, ACtiveX script or Visual Basic coding,visual basic is more powerful does they speed equivalent when import/export?

I haven't see ActivX script and any transformation that is consider dynamic yet, so don't know how limit is the DTS

ASTronus

ASKER

where can i find more info. about that, I want to know can DTS can handling complex system with lots of logic. and does it faster or the same.

ASKER CERTIFIED SOLUTION

PUMASOFT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial