April 5, 2010 at 9:36 am
Hi,
In DTS we use to run packages using sp_OACreate and sp_OAMethod. by passing in dynamically variables(file name or DTS name) how do we do equivalent of that in SSIS? Can stored procedured be used to run SSIS packages too?
Example:
--
-- Create the package
--
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
-- Load the Package
-- DTSSQLServerStorageFlags
-- DTSSQLStgFlag_Default = 0 (Use Server authenticdation)
-- DTSSQLStgFlag_UseTrustedConnection = 256 (Use Windows Authentication)
Set @mycmd = 'LoadFromSQLServer("' + @server + '", "", "", 256, , , , "' + @DTSPKG + '")'
EXEC @hr = sp_OAMethod @oPKG, @mycmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
If yes how do I specify my packge name and in which method? to run them
April 6, 2010 at 8:53 am
I had looked at this exact question about 18 months ago and didn't find a really good solution. What I did was write a SQLCLR module that instantiated a process object and effectively called DTEXEC. A process object is like xp_cmdshell. Now, by policy we weren't going to be turning on xp_cmdshell on this server. And even if we did, the app wasn't allowed to use it. I viewed this method as more secure than simple xp_cmdshell because it read a table that listed all packages it was allowed to run, was only capable of calling DTEXEC, and didn't use xp_cmdshell.
CEWII
April 6, 2010 at 12:59 pm
Thanks for the guidance. So I was not the only one with this problem.
April 6, 2010 at 1:58 pm
Not at all. It is possible to import all the required DLLs but it is really messy and I personally wouldn't.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply