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


Execute DTS package with SQL?

Posted on 2000-02-28
Medium Priority
Last Modified: 2013-11-30
can I execute a DTS package from ISQL? (not going through the SQL Manager)
Question by:chiche
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

Expert Comment

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.

Accepted Solution

chigrik earned 150 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
    print 'error create DTS.Package'

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

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
  print 'Execute failed'

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

618 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