loading dts package through command line

Question

loading dts package through command line

rameshkondru

SSC Enthusiast

Points: 174
More actions
February 2, 2004 at 10:17 pm

#59577
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.
1. open enterprise manger
2. Right click on dts
3. select all tasks
4. select open template
5. 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
Ramesh kondru

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

juliekenny SSCommitted Points: 1558 More actions · Answer 1

You could write a package to load packages using an activeX. Parameters (filename / package name) could be passed as global variables (use -A to pass global variables to the loading package). If this is a new server then you might want to keep the loading package as a structured storage file and run directly from that.

Beware though, I once thought that I wanted to do this myself but when you load packages in this way you loose all layout properties and text annotations so the package can be fairly unmanageable afterwards.

To run packages from the command line use the dtsrun command.

Julie Kenny

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 2

Hi Julie,

Thanks for your response. i need it without using activex. Purely with sql server utilities.

Thanks

Ramesh

juliekenny SSCommitted Points: 1558 More actions · Answer 3

Surely the activeX task will be there even if you are re-distributing dts on it's own. I'm not aware of any T-SQL that will load a package, so I think you need something to get at the object model.

Julie

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 4

Hi julie,

Thanks for you help, but my requirement is through command line only that can be either tsql or bcp or some thing else. i am new to sql server basically i am a oracle dba.

Thanks

Ramesh

noeld SSC Guru Points: 96590 More actions · Answer 5

I believe Julie is Right. You need to save the package through the Package (or Package2) object and Use the SaveMethod and to gain access to the object from SQL Server you can:

1. Use the sp_OA stored Procedures

2. Use an SQL Server Saved Package as explained above

Now there is an undocumented sp in msdb:

sp_add_dtspackage

whith Parameters:

@name sysname,

@id UNIQUEIDENTIFIER,

@versionid UNIQUEIDENTIFIER,

@description NVARCHAR(255),

@categoryid UNIQUEIDENTIFIER,

@owner sysname,

@packagedata IMAGE,

@packagetype int = 0

If you Know the ID of the Package BEFORE HAND, then you can create a temp table and import the Packagedata using textcopy utility

All that is in my opinion alot easier using the First Approach but hey this is just another Option!

My $0.02

* Noel

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 6

Hi noel,

Thanks for your reply, you are very close to my requirement. coluld you pls explaine me in detail how to achieve it by sp_add_dtspackage. I am unaware of obeject id.

Ex: A file c:\noel.dts should be import to database.

Thanks

Ramesh

philcart SSC-Forever Points: 47794 More actions · Answer 7

Ramesh

Firstly, why you can't do this in VBScript is a mystery. If you have SQL Server installed then you have both VBScript and JScript installed as part of the default DTS functionality. If you're worried about running the VBScript stand-alone, then place it into an ActiveXScript task within a DTS package stored in SQL Server.

You won't be able to use the sp_add_dtspackage stored procedure because,

a) You don't have the Package and Version ID's. These are GUID that are generated by the process which calls this procedure

b) You don't have the package detail in the correct format. DTS Packages are stored as binary objects in SQL Server.

The best you'll be able to do is use the sp_OA* procedure as mentioned earlier by Noel. You'll need to create a DTS package object, load the package from the file into this object using the LoadFromStorageFile method. Then save it to SQL Server using the SaveToSQLServer method.

Here's a short example how to do this,

declare @hr int -- return value
declare @obj int -- object refence

--Create a package object
exec @hr = sp_OACreate "DTS.Package",
   @obj OUTPUT

-- load the package from file
Exec @hr = sp_OAMethod
 @obj, -- refers to the object that was created
 'LoadFromStorageFile', -- the method we want to call
 null,
 @uncfile = '\\blah\blah.dts', -- use UNC filename
 @name ='uspcallpackage' -- the name of the package

-- Save the package to the server
Exec @hr = sp_OAMethod
 @obj, -- refers to the object that was created
 'SaveToSQLServer', -- the method we want to call
 null,
 @servername = '(local)', -- the name of the server
 @flags = 256 -- authentication type 256=trusted connection

-- clean up time
Exec @hr = sp_OADestroy @obj

Check out "OLE Automation Objects in Transact-SQL", "LoadFromStorageFile method" and "SaveToSQLServer method" in SQL Server Books Online.

--------------------
Colt 45 - the original point and click interface

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 8

Hi Phill Carter,

Thanks for sparing your valuable time. I saved it as a procedure and executed. The script was with minor changes.pls find it below.

create procedure dtsimport as

declare @hr int -- return value

declare @obj int -- object refence

--Create a package object

exec @hr = sp_OACreate "DTS.Package",

@obj OUTPUT

-- load the package from file

Exec @hr = sp_OAMethod

@obj, -- refers to the object that was created

'LoadFromStorageFile', -- the method we want to call

null,

@uncfile = 'c:\ramesh.dts', -- use UNC filename

@name ='ramesh' -- the name of the package

-- Save the package to the server

Exec @hr = sp_OAMethod

@obj, -- refers to the object that was created

'SaveToSQLServer', -- the method we want to call

null,

@servername = '(local)', -- the name of the server

@ServerUserName = 'SA',

@ServerPassword = 'abcd',

@flags = 256 -- authentication type 256=trusted connection

-- clean up time

Exec @hr = sp_OADestroy @obj

and executed that stored procedure without any parameters though there is no need. After that when i look for this package in the database i am unable to find it.The mentioned path and user info is correct. do i need to do any thing more or the process i followed was wrong pls through some light on it.

Thanks

Ramesh

noeld SSC Guru Points: 96590 More actions · Answer 9

Ramesh,

You have to use either Windows Integrated (Trusted Connection) or SQL Server Authentication NOT Both!!

Please Replace this:

Exec @hr = sp_OAMethod

@obj, -- refers to the object that was created

'SaveToSQLServer', -- the method we want to call

null,

@servername = '(local)', -- the name of the server

@ServerUserName = 'SA',

@ServerPassword = 'abcd',

@flags = 256 -- authentication type 256=trusted connection

to

Either

Exec @hr = sp_OAMethod

@obj, -- refers to the object that was created

'SaveToSQLServer', -- the method we want to call

null,

@servername = '(local)', -- the name of the server

@flags = 256 -- authentication type 256=Windows Authentication

OR

Exec @hr = sp_OAMethod

@obj, -- refers to the object that was created

'SaveToSQLServer', -- the method we want to call

null,

@servername = '(local)', -- the name of the server

@ServerUserName = 'SA',

@ServerPassword = 'abcd',

@flags = 0 -- authentication type 0=SQL Server Authentication

HTH

* Noel

philcart SSC-Forever Points: 47794 More actions · Answer 10

Well the sample I provided didn't check the return value from the sp_OA* procedures. The variable @hr will be zero if there were no errors. If you find an error, check the Books Online "OLE automation" entry I gave you earlier for how to return error information.

--------------------
Colt 45 - the original point and click interface

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 11

Thanks friends with slight modifications it worked out. I will post entire script soon.

rameshkondru SSC Enthusiast Points: 174 More actions · Answer 12

Hi,

For creating a dts package we are following

exec @hr = sp_OACreate "DTS.package"

If at we need to to store it to meta data services what should we do.

Thanks

Ramesh

noeld SSC Guru Points: 96590 More actions · Answer 13

noeld

SSC Guru

Points: 96590

February 6, 2004 at 7:29 am

#493064

Use the SaveToRepository Method!

* Noel

Dennis Link SSC-Addicted Points: 420 More actions · Answer 14

If you want to do this from the command line, you can use the DTSRUN program. Set up the file name as a dynamic parameter in the package by using a global parameter to get the filename from the command line and then use the dynamic properties task to assign the value in the global variable to the filename in the appropriate object.

Search for DTSRUN Utility in Books On Line for more information.

Good Luck!