Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Command line utility for adding DTS packages to a database

Posted on 2001-06-12
11
1,043 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 143

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 143

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 143

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 143

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 143

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 143

Expert Comment

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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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