Post reply

loading dts package through command line

  • 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

  • February 3, 2004 at 2:26 am

    #492576

    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

  • February 3, 2004 at 2:42 am

    #492580

    Hi Julie,

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

    Thanks

    Ramesh

     

  • February 3, 2004 at 2:51 am

    #492581

    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 

  • February 3, 2004 at 3:22 am

    #492585

    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

     

  • February 3, 2004 at 8:10 am

    #492621

    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

  • February 4, 2004 at 3:20 am

    #492722

    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

  • February 4, 2004 at 8:09 pm

    #492827

    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

  • February 5, 2004 at 4:33 am

    #492860

    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

  • February 5, 2004 at 8:42 am

    #492910

    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

  • February 5, 2004 at 2:02 pm

    #492959

    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

  • February 5, 2004 at 11:53 pm

    #493000

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

  • February 6, 2004 at 3:01 am

    #493016

    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

  • February 6, 2004 at 7:29 am

    #493064

    Use the SaveToRepository Method!

     


    * Noel

  • February 6, 2004 at 9:52 am

    #493118

    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!

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

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