Call a DTSX or SSIS Package from a Stored Procedure

Question

Call a DTSX or SSIS Package from a Stored Procedure

riccarrasquilla

Old Hand

Points: 309
More actions
November 2, 2008 at 7:17 am

#195318

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 -
0x800403EDMicrosoft Data Transformation Services (DTS) PackageNo Steps have been defined for the transformation Package.sqldts80.hlp700
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 -
0x80040504Microsoft Data Transformation Services (DTS) PackageThe specified file is not a Package Storage File.sqldts80.hlp704
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

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

riccarrasquilla Old Hand Points: 309 More actions · Answer 2

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.

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 3

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

riccarrasquilla Old Hand Points: 309 More actions · Answer 4

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.

ZodwaJostinaM SSC Enthusiast Points: 123 More actions · Answer 5

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