Solved

Execute DTS package with SQL?

Posted on 2000-02-28
2
425 Views
Last Modified: 2013-11-30
can I execute a DTS package from ISQL? (not going through the SQL Manager)
0
Comment
Question by:chiche
[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
2 Comments
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2567441
In SQL 7:

DTS allows the user to program transformations through two different interfaces. Programmers can create and modify DTS packages through an OLE Automation interface implemented in Dtspkg.dll (using any language that supports OLE Automation such as Microsoft® Visual Basic®). Programmers can also write directly to the data pump through a COM interface implemented in Dtspump.dll (using C or C++).

Both interfaces, the package and pump, are extensible. Programmers can create custom tasks for the package interface using Visual Basic or any other language. Programmers can also create custom transforms for the pump interface using C or C++ with Dtspump.h.

In your object browser, there is an OLE Automation package interface called the Microsoft DTSPackage Object Library (DTS) that is implemented by Dtspkg.dll.

There is also an additional interface used by Microsoft ActiveX® scripting tasks (called Microsoft DTSDataPump Scripting Object Library or DTSPump for short) implemented in Dtspump.dll.

Otherwise, you can use BCP in ISQL.
0
 
LVL 8

Accepted Solution

by:
chigrik earned 50 total points
ID: 2567444
Yes, you can.
It's example from my PC:

isql -U sa -P author -i "c:\StartPackage.sql"

where StartPackage.sql is:

"
DECLARE @object int
DECLARE @hr int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
    print 'error create DTS.Package'
    RETURN
END

EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile', NULL, 'C:\NewPack.dts', ''
IF @hr <> 0
BEGIN
    print 'error LoadFromStorageFile'
    RETURN
END

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
  print 'Execute failed'
  RETURN
END
"
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

696 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