Solved

Command line utility for adding DTS packages to a database

Posted on 2001-06-12
11
1,041 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 31
string fuctions 4 26
SQL Server: Unable to remove duplicate sets in Header/Detail 6 24
divide by zero error 23 16
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

810 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