Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Command line utility for adding DTS packages to a database

Posted on 2001-06-12
11
Medium Priority
?
1,060 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
[X]
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
  • 6
  • 5
11 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

609 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