Improve company productivity with a Business Account.Sign Up


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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

579 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