Call a DTSX or SSIS Package from a Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Call a DTSX or SSIS Package from a Stored...

Call a DTSX or SSIS Package from a Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

riccarrasquilla

Posted Sunday, November 02, 2008 7:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 11:24 AM
Points: 3, Visits: 17

I'm new to SSIS. When i used SQL 2000 and DTS, I was able to call a DTS package from a stored procedrue. Can I do the same with DTSX or SSIS packages. I've tried using the following code ->

======================================================================
DECLARE @hr INT, @oPKG INT, @Cmd VARCHAR(1000), @RetVal INT, @PkgName varchar(255), @oStep varchar(255)
Declare @GVOutput int, @out_error varchar(50)
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT

Set @PkgName = 'WriteFileToDisk'
SET @Cmd = 'LoadFromSQLServer("(local)", "", "", 256, , , , "'+@PkgName+'")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd , NULL

EXEC @hr = sp_OAMethod @oPKG, 'Execute'

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @oPKG

set @out_error = 'Error '
print @hr
END
else
begin
set @out_error = 'No Error'
end
print @out_error

EXEC @hr = sp_OADestroy @oPKG
====================================================================
When I run this code, I get the following error -
0x800403ED Microsoft Data Transformation Services (DTS) Package No Steps have been defined for the transformation Package. sqldts80.hlp 700

Also, I've used this code ->
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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:\WriteFileToDisk.dtsx', ''
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object

print 'error LoadFromStorageFile'
RETURN
END

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

I get this error -
0x80040504 Microsoft Data Transformation Services (DTS) Package The specified file is not a Package Storage File. sqldts80.hlp 704

I've tried using dtexec and xp_cmdshell. i get a success message but no package result. Here is that code.
_________________________________________________________________________________
Declare @cmd varchar(1000)

select @cmd = 'dtexec /F "c:\WriteFileToDisk.dtsx"'
exec master..xp_cmdshell @cmd

Any help would be appreciated.

Thx

Post #595555

Elisabeth Rédei

Posted Sunday, November 02, 2008 8:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175, Visits: 676

Hi,

Although the sp_OA methods or xp_cmdshell should work (providing OLE Automation and xp_cmdshell has been enabled) - why don't you set up an Agent Job that executes it, no schedule, and then use sp_start_job to run it? If this is the only situation you use sp_OA/xp_cmdshell you can then go ahead and disable them:)

There are some pitfalls and security details you need to be aware of - let me know if you want some pointers.

HTH!

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

Post #595574

riccarrasquilla

Posted Sunday, November 02, 2008 8:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 11:24 AM
Points: 3, Visits: 17

Thanks for the suggestion.

Here's what I'm trying to do.

I'm taking parameters from a web page and writing them to a txt file for security reasons via the sql server.

The web page calls a stored procedure (SP). The SP passes the parameters to the dtsx package via global variables.

The global variables are passed to a vb.net script. The script writes to a text file on the database server.

It's a run-time procedure unless I can think of another way to do it. Do you have any suggestions?

Again, thanks so much for the quick reply and assistance.

Post #595576

Elisabeth Rédei

Posted Monday, November 03, 2008 4:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175, Visits: 676

Hi again,

I'm not sure I quite follow the flow - do you have to pass the parameters via a stored procedure (assuming they are in the textfile)? Couldn't your package grab the variables itself?

This is not really my domain and I am sure there are other people "out there" with more valuable input!

In case you are still interested, here is a step by step on how to run a package as a scheduled job:
http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

Sorry I can't be of more help!

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

Post #595702

riccarrasquilla

Posted Monday, November 03, 2008 5:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 11:24 AM
Points: 3, Visits: 17

Thanks for your assistance.

The web page sends information to a stored procedure via parameters.

The stored procedure will receive parameters from the web page.

I want to take those parameters and pass them to the DTSX package (via global variables).

Inside the DTSX package, I have a script task that will write the global variables to a text file.

Hopefully, I did a better job of explaining my situation. Again, thanks for your information.

Post #595726

ZodwaJostinaM

Posted Wednesday, August 19, 2009 8:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 01, 2012 8:45 AM
Points: 9, Visits: 184

Hi, did you ever come right with this i have a similar task i.e passing parameters from SP/Web application to the package.

Post #773543

« Prev Topic | Next Topic »

Permissions