Importing Query From .sql file?

#41

On Jun 25, 2:49 pm, vaib <v... (AT) discussions (DOT) microsoft.com> wrote:

Quote:

Hi M.

ya it has worked.......Thanks 4 tat...........
Actually i used a activeX Script task lik this.........

Dynamic Task Property--------ActiveX script-------Oledb -----data
transform----text file.

Activex task is basically to open the sql file containing query and to read
query.

But nw i need to open various sql files containing queries(in local machine)
and insert the result of each sql query in a separate text file.
I m using global variables to pass the fetched query to data transform task,
tat u know already.......

dim oFSO
dim x
set oFSO = CreateObject("Scripting.FileSystemObject")
set x = oFSO.OpenTextFile(DTSGlobalVariables("gvQueryPath" ).value) 'contains

'C:\.sql

DTSGlobalVariables("gvQuery").value = x.Readline
x.Close

So wt shud be my approach....??
1. It can b a modification to this package....
2. Or I hv to start from scrach again........

Help........thanks in advance......

Vaib

"matteus" wrote:
On Jun 25, 6:49 am, vaib <v... (AT) discussions (DOT) microsoft.com> wrote:
"matteus" wrote:
On Jun 24, 3:37 pm, vaib <v... (AT) discussions (DOT) microsoft.com> wrote:
Hi
can u tell me how to fetch query from .sql file while working in DTS 2000?

actually, I need to fetch query from . sql file(on local drive) and then
want to execute this query on a sql database to export that sql data to a
.txt file using dts 2000.......

Hey ...give any comments.........yes, no, possible, not
possible....anything.........

For what i know anything is possible

and there're always a plenty
of ways to perform something.
In your case i would prefer using bcp.exe

But if you want to use DTS at any cost you should build a new package,
then:
- add an oledb connection to your server,
- add an execute sql task that uses this connection
- add a dynamic properties task that sets the sql statement of the
previous task to your .sql file
This task must be created after the execute sql task, but must
obviously be run before it

bye,
M.

----------------------------------------------------------------------
Hi M.
thanks 4 suggestions.......

actually i need to insert some data into text file taking it from sql
server........
i hv done it using "transform data task" making an ole db connection where i
wrote the required query into "transform data task" for fetching the data
from sql table and dumped the query result in a text file.........
Nw wt i want is .............
1. I need to fetch the same query from some .txt or .sql file in C:
Drive..........
2. soit means i dont want to write the whole query in dts package.......
3. i need to read it from an external file on my local drive.........
4. and then run this query in my dts package.......
5. dumping the result of this query in a text file......
I hv to use only dts.......
Help plz......thanks again 4 replying......

So if your pkg is like:
OLEDBconn ---- trasform data task (grey arrow) ----> TextFileConn

Then simply:
- add a dynamic properties task to your package
- select it and then your oledb conn, right click and add a OnSuccess
workflow constr
- double click on the DynPropTask, click the Add button
- on the left side choose tasks and the transform data task (the name
should be DTSTask_DTSDataPumpTask_X or similar)
- once youve chosen the TransformDataTask, on the right side go down
and select SourceSQLStatement property and click on Set...
- Then set the value of the property to your .sql file

I never did something similar but this should work. I believe you also
need to tune security/permission for the user that runs the package,
in order to be able to read the file onto your local dirve.

Try and let us know
M.

no you should start from the one you have shown, but if you have to
loop through various(=many) .sql files i don't know a quick method of
doing it (perhaps with an activex), if you mean various=a bunch then
delete activex task and crete a DynPropertiesTask, Trasformn Data Task
and File Destination each .sql file

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Importing Query From .sql file?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Importing Query From .sql file? - 06-26-2008 , 01:56 AM