SHARE
TWEET

Execute a DTS from SQL

priore Apr 28th, 2012 134 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --
  2. --  Created by Danilo Priore.
  3. --  Copyright (c) 2012 Prioregroup.com. All rights reserved.
  4. --
  5. --  Permission is hereby granted, free of charge, to any person obtaining a copy
  6. --  of this software and associated documentation files (the "Software"), to deal
  7. --  in the Software without restriction, including without limitation the rights
  8. --  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  9. --  copies of the Software, and to permit persons to whom the Software is
  10. --  furnished to do so, subject to the following conditions:
  11. --
  12. --  The above copyright notice and this permission notice shall be included in
  13. --  all copies or substantial portions of the Software.
  14. --
  15. --  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  16. --  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  17. --  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  18. --  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  19. --  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  20. --  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  21. --  THE SOFTWARE.
  22. --
  23. --  Use:
  24. --  exec _spStartDTS "[MY-SERVER\MY-DBSERVER]", "sa", "sa", "my-DTS"
  25. --
  26. CREATE PROCEDURE _spStartDTS(
  27.         @server varchar(200),
  28.         @user varchar(30),
  29.         @password varchar(30),
  30.         @name varchar(200)
  31. )
  32. AS
  33. DECLARE @oPKG int
  34. DECLARE @hr int
  35. DECLARE @cmd varchar(100)
  36. SET @cmd = 'LoadFromSQLServer(@server, @user, @password, 256, , , , "[' + @name + ']")'
  37. EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
  38. IF @hr <> 0 BEGIN RETURN @hr END
  39. EXEC @hr = sp_OAMethod @oPKG,@cmd,NULL
  40. IF @hr <> 0 BEGIN RETURN @hr END
  41. EXEC @hr = sp_OAMethod @oPKG, 'Execute'
  42. IF @hr <> 0 BEGIN RETURN @hr END
  43. EXEC @hr = sp_OADestroy @oPKG
  44. RETURN 0
  45. GO
RAW Paste Data
Top