Execute DTS package with SQL?

Posted on 2000-02-28
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 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
    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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2008 SSIS import 11 49
SQL Server and getting client IP Address 6 51
Using Aggregate Functions to Count 3 32
Splitting the content of a column in SQL 11 20
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now