A very tricky question:Can I generate a script through code and exec?

[joelwenzel]

Ok, I am setting up a DTS that must transfer data from one database to another. The thing is both databases are huge and fields must be multiplied, sumed, etc during the transfer. I started to solve the problem by using a large activeX script that was designed to handle all the cases but it was way to long and did not really allow for a lot of expansion. So instead, I've come up with this idea:

Have a lookup table that has the following fields

ToTable (table data being transferred to)
ToTableFieldName (the new field name in the new table)
Commands (a text field contain a bunch of TSQL commands that return the new value)

I then will have a vbscript that cycles through each element in the lookup table, and writes the commands in the proper format so that the return values can be inserted into the ToTable. The vb script will generate a TSQL script.

First, is there a better way of doing this? This way seems pretty good to me and seems to be easily changable

Second, how do I actually go about doing this? I need a way to load a TSQL file (that is generated from the vbscript) and execute it without having to do it manually. The TSQL file may contain procedures and functions so I cannot really execute it line by line.

 

[JohnDTampaBay]

Is it possible to write a procedure that compiles exactly the data you want to transfer (aggregations and all) into tables, then transfer the tables in full?

Do this do the trick or did I miss something?
1 - Create an ExecuteSQL task that runs after the ActiveX
2 - At the end of the ActiveX script, set the SQLStatement property of the ExecuteSQL task to the script just created.


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[joelwenzel]

Chances are, I missed something, not you I think the real tricky part of my question is if there is a better way to solve my task. But trying to implement this (the second question) is still really tricky for me at least

Anyway, I could call an ExecuteSQL task but it has to be able to execute a file that was just generated by the vbscript (or by an app or something). Is there some way to import the file?

Also, one other thing is that an ExecuteSQL task requires a connection. I am very inexperienced with this (I've been usin vbscript) but I need two connections, one for the db I'm comming from and the other for the one I am going to. Is it possible to get these connections in the code?

 

[joelwenzel]

ok, for anyone else wondering this

TSQL code:

EXEC master.dbo.xp_cmdshell 'osql -U sa -P "" -i E:\test.sql'

I haven't tested it much but it seems to work pretty good

after -U is the username
and after -P is the password

 

[MDXer]

Why not create Views that extract the data in the way you would like and then in your dts specify your source as being the view.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck