Run SSIS package in a stored procedure

By: dotrunghieu on 10:56 AM

There are 3 ways to run SSIS package:
- in an other ssis package
- in a stored procedure
- in batch file
When I test the second way, (run a ssis package in a stored procedure)
- I create a SSIS package named Load_VPB_Customer
- I create a procedure to call  this package
The code for a stored procedure here:

CREATE  PROCEDURE [dbo].[ExePakageLoadCustomer1]
AS
BEGIN
       SET NOCOUNT ON;
    Declare @cmd varchar(1000),
            @p_File_Path varchar(1000),
            @p_File_name varchar(100)
    set @p_File_Path='D:\ETL\TestLoadCustomer\TestLoadCustomer\'
    set @p_File_name='Load_VPB_Customer.dtsx'
    select @cmd =  'dtexec /F "' + @p_File_Path + @p_File_name + '"'
    exec master..xp_cmdshell @cmd
   END

 When executed   the stored procedure I encountered with some error about encription user key. Like that: "   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that..."
And the solution is:
- change property of ssis package:ProtectionLevel default is EncryptSensitiveWithUserKey





 You change to EncryptAllWithPassword
And type value for pakagePassword: (my password is admin123)
Rebuild the package
- The next step is changing code for the stored procedure.
Modify the code that call ssis packag:
select @cmd =  'dtexec /DECRYPT "admin123" /F "' + @p_File_Path + @p_File_name + '"'
- Rerun the package and I work.