Solved

Command line utility for adding DTS packages to a database

Posted on 2001-06-12
11
1,033 Views
Last Modified: 2013-11-30
Hi,

I need some scripts to automatically add "items" to a database.
I'm using osql to add tables, stored procedures, ...

I'm looking for a command line utility that allows me to add a DTS package to a SQL server, based on either the VB script that I can save when creating the DTS, or based on the .dts file of the package.

Any ideas ?
0
Comment
Question by:vindevogel
  • 6
  • 5
11 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6181532
If you use SQL 2000, i know that there is a flag in the DTSRUN utility to load a .dts file to the server...

In vb, you "simply" do this:

dim dtsPackage as DTS.Package
set dtsPackage = new dts.Package
dtsPackage.LoadFromFile ( filename )
dtsPackage.SaveToSQLServer ( someparams )
set dtsPackage = nothing

i don't have the BOL here, so i need to look up this when i'm in the office...

CHeers
0
 
LVL 4

Author Comment

by:vindevogel
ID: 6181746
angellll :

The VB does not work in a batch file ....
This question is related to another one, where I have Installshield doing an installation of ASP's, components, Tables in SQL, ... At the end of the process, I need to run a batch file, using eg. osql to create Stored Procedures on my server.

If you can find that flag for me, that would be great.  We're using 2000, primarly.  I'll take a look myself too.

Another thing could be of course, with the new .net, to write a command line utility incorporating the VB code.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6182351
in fact, you can save a dts to file using the /S, /F and /!X parameters together.

Sorry for the confusion, but i might propose the following stored procedure that normally executes a package using TSQL stored procedure. You might modify it to save it to file, by commenting the line that calls the execute method, and uncomment the line that would call the SaveToStorageFile method (and maybe add the parameter to the stored procedure:

CREATE PROC     ExecutePackage
     @Package     VARCHAR(50),
     @Server          VARCHAR(50) = NULL,
     @User          VARCHAR(50) = NULL,
     @Password      VARCHAR(50) = NULL
AS
DECLARE @P     INT
DECLARE @hr     INT
DECLARE @source VARCHAR(200)
DECLARE @description VARCHAR(2000)

SET @Server = COALESCE (@Server, @@servername )

EXEC @hr = sp_OACreate 'DTS.Package', @P OUTPUT
IF NOT (@hr = 0)  RAISERROR ('Package Object could not be created',16,1) WITH SETERROR

IF (@User IS NULL) AND (@Password IS NULL)
   EXEC @hr = sp_OAMethod @P, 'LoadFromSQLServer', NULL, @ServerName=@Server, @Flags=256 ,@PackageName=@Package
ELSE
   EXEC @hr = sp_OAMethod @P, 'LoadFromSQLServer', NULL, @ServerName=@Server, @ServerUserName=@User , @ServerPassword=@Password, @Flags = 0,@PackageName=@Package

IF NOT (@hr = 0)  
  BEGIN
  EXEC      sp_OAGetErrorInfo @P, @source OUT, @description OUT
  SET @Description = 'Package could not be loaded : ['+ @Source + '] ' + @Description
  RAISERROR (@Description,16,1) WITH SETERROR
  END

EXEC @hr = sp_OAMethod @P, 'Execute'
-- EXEC @hr = sp_OAMethod @P, 'SaveToStorageFile' , NULL, 'c:\test.dts'

IF NOT (@hr = 0)  
  BEGIN
  EXEC      sp_OAGetErrorInfo @P, @source OUT, @description OUT
  SET @Description = 'Package could not be Executed : ['+ @Source + '] ' + @Description
  RAISERROR (@Description,16,1) WITH SETERROR
  END

EXEC @hr = sp_OADestroy @P
IF NOT (@hr = 0)  RAISERROR ('Package Object could not be destroyed',16,1) WITH SETERROR

GO

Cheers
0
 
LVL 4

Author Comment

by:vindevogel
ID: 6230642
dtsrun /S MapServer /U sa /P /F c:\test.dts /!X

This does not work : "The specified DTS package does not exist"


!X is for not executing, but for saving the package to a file, not the other way round.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6231014
> you can save a dts to file using the /S, /F and /!X parameters together.
> !X is for not executing, but for saving the package to a file
The 2 sentences are equal??

As far as i know, you can execute a package from file,
by modifying the stored procedure i gave you:
LoadFromSQLServer ...
must be replace by
LoadFromFile 'File'

Cheers
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Author Comment

by:vindevogel
ID: 6231154
Angelll ... I want a .dts file as base and create a DTS package onto my server with the file ...

That's not possible with DTSRun (save to a file and not load from a file)


I want a blank SQL, type at the command prompt: myTool /Server=MyServer /File=myFile.dts
After that, a DTS package must have been created on the server (that did not exist before).

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6231189
If you give me the time to return to the office, i can send you a VB exe that does this... Surely it needs the msvb6.dll in the same folder as the exe, but nothing else (if started on the server).

I agree on the DTSRun unable to load the package to the server...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6232430
i send you the files, but post the main VB code extract here:

  Dim objPackage As Object
  Dim strUserID As String
  Dim strServer As String
  Dim strPWD As String
  Dim strFile As String
 
  'split the command$ parameter to fill the variables...
  <my code goes here>

  'now load the package...

    Set objPackage = CreateObject("DTS.Package")
    objPackage.LoadFromStorageFile strFile, ""
    objPackage.SaveToSQLServer strServer, strUserID, strPWD

Cheers
0
 
LVL 4

Author Comment

by:vindevogel
ID: 6234454
AngelIII

We've managed to do this ourselves .... but based on your idea.  Basically, it's simple.  You can run VB code from the command line ... if you use the VB script engine

This is the script we've written, saved into a PkgInst.vbs
It works from the command line  :-)



Option explicit

dim ServerName
dim DTSLocation
dim Pkg
dim PkgServerside

if (WScript.Arguments.Count = 2) then
    ServerName = WScript.Arguments.Item(0)
    DTSLocation = WScript.Arguments.Item(1)

    set Pkg = createObject("DTS.Package")
    set PkgServerside = createObject("DTS.Package")
    Pkg.FailOnError = False

 ' First argument = File UNC
 ' Second argument = Password
    Pkg.LoadFromStorageFile DTSLocation,"sa"
 
    on error resume next
    PkgServerside.RemoveFromSQLServer ServerName, "sa",, , , ,Pkg.name
    on error goto 0
    Pkg.FailOnError = True
    Pkg.SaveToSQLServer Servername,"sa"

   set Pkg = nothing
   set PkgServerSide = nothing

 else
    msgbox "Enter the server name as first argument, the DTS-File location as the second argument"
 end if




Thanks a lot for the help.
I sent you a mail concerning the exe.
0
 
LVL 4

Author Comment

by:vindevogel
ID: 6234455
Eventually, it's almost your code we're running in the VBS. So, you've really earned these points !

Thanks a lot
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6234464
Glad i could help!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now