Dynamic Qry for DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Dynamic Qry for DTS

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2003-07-07 : 05:20:27

hi

i need to pass the query to my dts package in runtime.. the query in stored in text file . or the DTS should read query from file.

the process shd be open the file read the query
and then using the query run the package.

i have diff source so i need a dynamic sql... any one has done before
i have seen the forum and dtsrun but of no use.... in my case i am passing the whole query.. is it possible

======================================
Ask to your self before u ask someone

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-07 : 07:50:04

You are much better off using bcp for this. It is much more flexible for handling dynamic properties than DTS is. The only downside to bcp is that you cannot transfer directly from one server to another (you can only import/export to files), however you can accomplish this as a two-step process, one to export and another to import to the other server.

We'll need a little more background on what you're doing to provide a more detailed answer.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-07 : 14:53:47

Dim dt As New DTS.Package
dt.LoadFromSQLServer "(local)", "sa", "", , , , , "myPackage"
' overwrite existing SQL statement of Execute SQL Task...
dt.Tasks(1).Properties(3) = "insert into myTable select '234', '567'"
dt.Execute
dt.UnInitialize

Is not it simpler to use something like this as SQL statement of Execute SQL Task...:

declare @fso int, @f int, @ss varchar(8000)
exec sp_OACreate 'Scripting.FileSystemObject', @fso out
exec sp_OAMethod @fso, 'OpenTextFile', @f out, 'C:\query.txt'
exec sp_OAMethod @f, 'ReadAll', @ss output
exec sp_OADestroy @f
exec sp_OADestroy @fso

exec(@ss)

- Vit

Edited by - Stoad on 07/07/2003 17:53:22

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-07 : 14:58:22

quote:

The only downside to bcp is that you cannot transfer directly from one server to another (you can only import/export to files), however you can accomplish this as a two-step process, one to export and another to import to the other server.

It is my understanding that DTS uses bcp anyway, so it is also doing a two-step process but it appears as one-step in the package.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources