Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 24, 2000

Data Transformation Services (DTS)

By Darren Green

Overview

The most common method used to execute DTS packages from within T-SQL scripts or stored procedures, is via xp_cmdshell and dtsrun. Whilst this works, and is very simple the implement, it lacks flexibility. The SQL Server 2000 version of dtsrun allows you to pass GlobalVariables on the command line, which improves it's appeal, but the security restrictions on xp_cmdshell can still limit it's appeal. This article demonstrates the OLE stored procedures and some of the benefits of using them.

The OLE stored procedures are a very powerful mechanism to access resources outside of those core to SQL Server, and this article demonstrates their use in relation to DTS. Whilst everyone else know refers to COM, not OLE, the SQL Server Books Online still uses the term OLE Automation, and the stored procedures are prefixed with sp_OA.

All samples use the OLE Automation Return Codes and Error Information stored procedures listed in SQL Server Books Online. The main procedure, sp_displayoaerrorinfo, takes the return code from any of the standard OLE Automation stored procedures, and displays the error Source and Description using sp_OAGetErrorInfo. It also calls sp_hexadecimal to convert the integer error code into a string (char) representation of the true hexadecimal value.

For clarity variable declaration has been removed from the code snippets below

Creating the DTS Package Object:

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
    PRINT '***  Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

Loading the Package:

-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256 
EXEC @hr = sp_OAMethod @oPKG, 
  'LoadFromSQLServer("MyServer", "", "", 256, , , , "MyPackage")', 
  NULL
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

Executing the Package:

EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
    PRINT '***  Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN
END

Cleaning up:

EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
    PRINT '***  Destroy Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

 

You'll notice that my style of calling methods is to feed the whole command, complete with parameters as a character string. The alternative to this is illustrated below, Loading the Package:

EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSqlServer', NULL, 
  @ServerName='MyServer', @PackageName='MyPackage', @Flags=256
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM