How to run a script file inside dts

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)

How to run a script file inside dts

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-03 : 12:37:54

I think the topic says it all.

I have my queries in .sql file, How can I run those files inside dts package?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

mfemenel
Professor Frink

1421 Posts

Posted - 2005-05-03 : 14:04:29

You can put an "Execute Process Task" into your package and use it to call a batch file. Your batch file will look something like this:
for /F "tokens=1,2 delims=," %%a in (c:\cr160_Deploy\servers\servers.txt) do for %%z in (c:\cr160_Deploy\scripts\f_disposition.sql) do osql -E -n -d%%b -S%%a -i %%

In this case I use a file called scripts which contains a server name, database name like this:
servername,dbname. For each combination of server, database it will run whatever you put in your sql script (in this case f_disposition.sql).

Thanks to robvolk for showing me the light on the wonder that is the batch file a few months ago. Note:no robvolks were harmed in the writing of this post.

Mike
"oh, that monkey is going to pay"

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-03 : 23:08:11

Mike,

I didnt understand what the batch file is trying to do


for /F "tokens=1,2 delims=," %%a in (c:\cr160_Deploy\servers\servers.txt) 
do
for %%z in (c:\cr160_Deploy\scripts\f_disposition.sql) 
do osql -E -n -d%%b -S%%a -i %%

I assume this is how the batch file will look like.
f_disposition.sql is the sql script file
What is servers.txt? what does it contain?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

mfemenel
Professor Frink

1421 Posts

Posted - 2005-05-04 : 10:19:07

servers.txt contains a list of servers and databases that you want to deploy to. For example in my environment it contains atlmdch5,cxp_procare. Where atlmdch5 is the server name and cxp_procare is the database name. You don't have to do it this way if you don't want to. This particular exmaple is from a setup I use to deploy code to multiple servers at a time instead of going to each server, database and running the .sql files individually. I have 4 servers and 8 or so databases to make changes on so this saves me a ton of time.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources