Execute an SSIS package from a stored procedure without using XP Command Shell

The goal was to execute a package from a stored procedure without utilizing XP Command Shell. Utilizing the command shell is too much of a security concern.

The stored procedure is executed from an Excel workbook macro using VBA code. The macro begins the execution of the stored procedure while also passing two parameters from an Excel worksheet which are passed along to the SSIS package.

One of the parameters that are passed is the path to the Excel workbook on the shared drive it resides on. One of the initial tasks of the package is to copy the workbook from the shared server to the SQL server so that data can be extracted from it, transformed, and loaded to a SQL server database.

 

RESOLUTION

The final solution was to create a SQL Server Agent Job to run the package and then let the stored procedure start the job. The difficult aspects of this solution were passing the parameters to the job and getting around permissions errors.

 

Passing parameters to the job

There is no direct way to pass parameters to a job from within a stored procedure. The way around this was to create an environment that contains variables where the SSIS package can pull the necessary parameters from. These environment variables can then be modified by a stored procedure before the package runs.

To create a new environment:

  1. Navigate in the Integration Services Catalogs to the folder that contains the SSIS project and expand the project folder.
    1. A
  2. Right click on the Environments folder and click Create Environment
    1. Enter an environment name and optional description
  3. Close the popup and then right click on the newly created environment and click properties
    1. Under variables enter variable name and an initial value (these values will be changed later by the stored procedure)
    2. Keep in mind that the variable names do not have to match the parameter names within the package
      1. B
    3. Click OK and the new environment is created.

 

Configure the package to use the environment variables at runtime as its parameters:

  1. Expand the projects folder and right click on the project that contains the SSIS package and click configure.
  2. Click on References in the project configure pop-up and add the environment that was just created.
    1. C
  3. Next, click on Parameters and bring up the package that the environment variables will be passed to.
  4. Click on the ellipses next to the parameter and choose to use environment variable.
  5. Next simply choose the correct environment variable for that parameter and click OK.
    1. D
  6. Do this for all package parameters that will be passed by the stored procedure.

 

 Set_Environment_Variable_Value

The final task is to tell the stored procedure to change the environment variables to whatever values are passed to it by the Excel macro. This is fairly straightforward since SQL Server has a built-in stored procedure that will do just that, conveniently named set_environment_variable_value.

The context for the set_environment_variable_value is:

exec ssisdb.catalog.set_environment_variable_value

       @folder_name = ‘folder name’,

       @environment_name = ‘name of your environment’,

       @variable_name = ‘name of environment variable’,

       @value = @value_to_be_passed

 

The entire stored procedure which sets the environment variables and executes the job can be seen here:

CREATE PROCEDURE [dbo].[publish_IOGrid_Test]

       — Add the parameters for the stored procedure here

       @SourceFilePath nvarchar(200) = ”,

       @Cycle nvarchar(100) = ”

AS

BEGIN

       SET NOCOUNT ON;

exec ssisdb.catalog.set_environment_variable_value

       @folder_name = ‘FOX’,

       @environment_name = ‘Load_IOGrid_Env’,

       @variable_name = ‘current_cycle’,

       @value = @Cycle

exec ssisdb.catalog.set_environment_variable_value

       @folder_name = ‘FOX’,

       @environment_name = ‘Load_IOGrid_Env’,

       @variable_name = ‘Excel_Source’,

       @value = @SourceFilePath

WAITFOR DELAY ’00:00:05′; –waits for the variables to be set before starting the job

Execute msdb.dbo.sp_start_job N’Publish_IOGrid_Job’; –starts the job

END

 I skipped over the task of creating the job that runs the package because this is fairly easy to do.  The complicated part of the job configuration came about while trying to get past the permission errors, which is discussed next.

 

Permission Errors

There were a number of errors that came about while trying to get this package to be executed from within a job, the job to be executed within a stored procedure, and the stored procedure to be started by an Excel macro.

Following are the errors that came up, and the steps taken to get past each:

  1. SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “New_Excel_Source” failed with error code 0xC0209303. 
    1. The package execution was causing errors because it was being run in 64-bit mode which does not have all the functionality as 32-bit mode.
    2. Solution: run the package in 32 bit mode.
      1. Go to the job properties page (right click on job and click properties)
      2. Click on steps and edit the step that runs the package
      3. Click on the Configuration tab, and within that click on the Advanced tab
      4. Then, click the 32-bit runtime box and click OK
        1. E

 

  1. Access to the path ‘Shared Server Path Name’ is denied.
    1. The account that is trying to execute the package does not have permission to access the shared server.
    2. Solution: to create a proxy for the job to run under that utilizes credentials which have permission to access the necessary network servers.
      1. First, create a new credential in Management Studio: expand security folder, right click on credentials, and select new credential. Enter a name for the credential, a domain identity that has access to the necessary servers, and the identity’s password.
        1. F
      2. Next, create a new proxy. Expand the SQL Server Agent folder, right click on Proxies, and select New Proxy.  Enter a name for the proxy, enter the credential name that was just created, and select the SQL Server Integration Services Package subsystem.
        1. G
        2. H
      3. The last step is to instruct the job to run as the newly created proxy account. Right click on job and select properties. Edit the step that runs the package and select the new proxy account in the ‘Run as:’ drop down menu, then click OK.
        1. H
      4. The package should now have no problem accessing the necessary shared server.

 

  1. Cannot access the package or the package does not exist. Verify that the package exists that that the user has permissions to it.
    1. This error occurred because the identity that I used to create the credential above did not have permission to access the SSISDB database where the packages are stored.
    2. Solution: Map the credential user to the SSISDB database and grant it permissions.
      1. Right click on the login name used for the credential above and select properties.
      2. Select User Mapping and map the user to the SSISDB database with ‘public’ database role membership.
      3. Next, expand the Integration Services Catalogs folder and navigate to the folder that contains the SSIS project.
      4. Right click on the folder and select properties.
      5. On the properties panel select permissions. Browse the logins and roles and select the credential login and press OK
      6. In the permissions screen, grant the credential login read and modify permissions to this project folder and press OK.
        1. I
      7. This login now has access to the appropriate package.

 

  1. Run-time error ‘-2147217900 (80040e17)’: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
    1. This error occurs in Excel when trying to run the macro that executes the stored procedure which contains the job.
    2. Solution: In order to access the SSISDB database from this outside application, the connection string must use login credentials that utilize windows authentication and not SQL Server Authentication.
      1. Old ConnectionString = “Provider=SQLOLEDB; Data Source=” & Servername & “; Initial Catalog=” & DatabaseName & “; User ID=” & UserId & “; Password=” & Password & “; Trusted_Connection=No
        1. This old connection string had a UserID that used SQL Server Authentication
      2. New ConnectionString = “Provider=SQLOLEDB; Data Source=” & Servername & “; Initial Catalog=” & DatabaseName & “; User ID=” & UserId & “; Trusted_Connection=Yes
        1. This new connection string has a User ID that uses Windows Authentication. No password is included and trusted_connection = yes.
      3. Once these changes are made, the macro should work with no errors. The VBA code for the macro is below for reference (server-names and other sensitive data is omitted).

 

J

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s