imprting a dts package - 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)

imprting a dts package

Author

Topic

rameshkondru
Starting Member

4 Posts

Posted - 2004-02-02 : 06:23:41

Hi,

I have a dts package on c drive which has to be imported to database through command line, can any one guide me.

Thanks
Ramesh

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-02 : 06:50:43

What do you mean by imported? Added to msdb?

See http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html
This loads a package - you can use the save to sql server method to save it to msdb and run it via osql to get a command line process.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rameshkondru
Starting Member

4 Posts

Posted - 2004-02-02 : 23:46:40

Hi NR Thanks for your reply .is it possible without creating a procedure.

rameshkondru
Starting Member

4 Posts

Posted - 2004-02-03 : 00:18:15

Hi,

I have a dts file on a particular drive which should be loaded to database through command line.From gui we generally follow follwing steps.

open enterprise manger
Right click on dts
select all tasks
select open template
browse and select required file and save in to db
I need this task to be done from command line can any one guide me.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-03 : 06:17:58

Why do you want it added to the database.
If you run it from a file instead then you just copy it into a directory - and it'll be faster to load too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-03 : 07:33:57

From command line? You definitely need a simple VBS script
(not sure in the syntax for an argument (path to a package file)
of command line):

Set s = CreateObject("SQLDMO.SQLServer")
Set p = CreateObject("DTS.Package")
p.LoadFromStorageFile ... ...
s.Connect "(local)", "sa", ""
p.SaveToSQLServer ... ...
s.Disconnect
Set p = Nothing
Set s = Nothing

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-03 : 08:30:23

Set arg = WScript.Arguments
Set s = CreateObject("SQLDMO.SQLServer")
Set p = CreateObject("DTS.Package")
p.LoadFromStorageFile arg(0), ...
s.Connect "(local)", "sa", ""
p.SaveToSQLServer ... ...
s.Disconnect
Set p = Nothing
Set s = Nothing

Then just run: my.vbs "D:\Test\my.dts"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-03 : 15:40:47

a bit overdone it I believe..

Set arg = WScript.Arguments
~~Set s = CreateObject("SQLDMO.SQLServer")~~
Set p = CreateObject("DTS.Package")
p.LoadFromStorageFile arg(0), ...
~~s.Connect "(local)", "sa", ""~~
p.SaveToSQLServer ... ...
~~s.Disconnect~~
Set p = Nothing
~~Set s = Nothing~~

Then just run: my.vbs "D:\Test\my.dts"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-03 : 16:05:31

You don't need VBScript for this.

From command line, just use dtsrun.exe to run a DTS package. It allows you to pass a filename.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-03 : 16:26:46

quote:
.... to run a DTS package

How about to save it into msdb?
Though, I'm not a specialist in the dtsrun.exe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-03 : 16:29:04

The point is that you don't need to load it into msdb as Nigel mentioned.

Tara

rameshkondru
Starting Member

4 Posts

Posted - 2004-02-04 : 01:24:29

Tara,
Thanks for your suggestion but i need to store it in msdb. i know that either by dtsrun or by xp_cmdshell we can run the package. Package should be scheduled for that i need to store inmsdb, so pls guide me if you know how to store in msdb through command line.

Thanks
Ramesh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-04 : 12:44:53

Stoad gave you the solution already. Run his VBScript from the command line.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-04 : 13:26:50

>> Package should be scheduled for that i need to store inmsdb
Nope - you will schedule a dtsrun command. dtsrun can load a package from the msdb or from a file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources