Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Call a DTSX or SSIS Package from a Stored Procedure Expand / Collapse
Author
Message
Posted Sunday, November 2, 2008 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, November 2, 2008 8:12 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:00 AM
Points: 177, Visits: 754
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
Posted Sunday, November 2, 2008 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 3, 2008 4:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:00 AM
Points: 177, Visits: 754
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
Posted Monday, November 3, 2008 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 19, 2009 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 1, 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 »

Add to briefcase

Permissions Expand / Collapse