3 Easy and Secure Ways to Make Users Run SSIS Packages

Have you ever come across a situation where you needed to make users run SSIS packages? This happens when the user is not comfortable with a fixed schedule for whatever reason. You can’t define if it’s every weekend, at 9 pm, or the 5th of the month. It happens when it happens.

This sounds problematic, depending on the volume of data. If you know that a million or so records won’t be advisable to process any time of the day, say no. But if the data volume is not a problem, what are your options?

A soft reminder: if you are not an administrator, you can’t run SSIS packages.

While all situations are different, this post suggests three possible ways to do it. Each will have its advantages and disadvantages. So, why not check it out, including the examples?

Let’s start.

1. Make Users Run SSIS Packages Using a File

Let’s begin with a typical scenario of extracting files. SSIS packages are suitable for extracting data in a file and loading it into a relational database, like Excel spreadsheets to SQL Server. This way, users can trigger the package execution when the file is ready for extraction.

What You Need

• A file like an Excel spreadsheet (.xlsx) and a shared folder/FTP location, where the user will copy it.
• An SSIS package that extracts the data from an Excel file to SQL Server. You schedule this in SQL Server Agent. You and your user have to agree on the frequency – it can be twice a day, at lunchtime, or whenever.
• Only one user copies the file to the shared folder to avoid duplicate processing.

Advantages

• This is the simplest way of all the three approaches, depending on your requirements.
• You only need to secure the shared folder or FTP location.

Disadvantages

• It limits your options to file input. The approach is not applicable either if the data come from the relational database.
• Timing can be an issue. If the user is 1 second late to the next execution, they must wait for the next scheduled run.
• Errors can occur for manually created files.

SSIS Package Example

Have a look at a simple example of using SSIS to extract the data from a file into a relational database:

The SSIS Package should have:

PACKAGE VARIABLES

1. filename – string. It stores the file name and the full path.
2. isFileExists – a boolean variable that determines if the file exists (true) or (false).

CONNECTION MANAGERS

1. A database connection to the relational database.
2. A connection to open the Excel file.
3. An SMTP connection to send the user and admin emails.

SCRIPT TASK – Check if File Exists

The first Script Task checks if the file exists. See Figures 2 and 3 for the Script Task Properties.

1. Include the filename package variable to ReadOnlyVariables.
2. Include the isFileExists package variable to ReadWriteVariables.
3. In the code, add a namespace for System.IO.
4. Test the file for existence, using file.Exists.
5. If the file is found, set isFileExists to true.

PRECEDENCE CONSTRAINTS

You need two Precedence constraints with an Evaluation operation set to Expression.

1. If isFileExists is true, proceed to the Data Flow Task to process the file:

2. If the file does not exist (Expression = !@[User::isFileExists], execution will end. So, email the admin about it (it is optional):

DATA FLOW TASK

This will include opening the Excel file, extracting and transforming the data, and dumping it into a SQL Server table.

SCRIPT TASK – Move the processed file to the Processed folder

The file copied by the user will move to a Processed folder after extraction. Why? This avoids reprocessing the file.

SEND EMAIL TASK

Email the user of the successful processing.

After designing and testing the package, deploy it in the SSIS Catalog (SSISDB) using Visual Studio. Then, go to SQL Server Agent and add a job for the package with the step and the schedule.

That’s it — a simple and straightforward way to make users run SSIS packages.

But what if we don’t have a file to process?

2. Make Users Run SSIS Packages by Using a SQL Table

The second approach is to use a table instead of a file. The SSIS package checks for new records from the table. Then, processing proceeds. And lastly, records are tagged as processed.

What You Need

• At least 1 table that the SSIS package will check. You may need more, depending on your requirements
• The app will write to the table with all information required by the SSIS package.
• The SSIS package is scheduled to run regularly. It could be 2x daily, or every 15mins, depending on the requirements or as agreed with users.

Advantages

• No “processing flat files” limitation. Any data source will do – an SQL Server or any relational database, Excel, text file, Sharepoint list, you name it.
• More flexibility than in option 1.
• The possibility to allow multiple users.

Disadvantages

• You need an app to do the INSERT in the designated table, and it could take some time to do this. If you need queuing, it will take longer.
• Timing can still be an issue.

SSIS Package Example

Figure 6 below shows the general design of simple requirements. We use a table with columns postTime, databaseUser, and object.

The package should include:

PACKAGE VARIABLES

1. tableData – object type. It is the container of our recordset.
2. postTime – DateTime. It relates to the current value of the postTime column.
3. user – string. It relates to the current value of the databaseUser column.
4. object – string. It relates to the current value of the object column.

CONNECTION MANAGER

You need at least 1 ADO.Net connection to SQL Server or any relational database.

DATA FLOW TASK – Get Table Data

It retrieves the log table records and put them in the record set:

The Recordset Destination has the following properties:

• Include three columns we need. Note the arrangement of the columns in the recordset. You will need it in the ForEach Loop Container later.

• Set the recordset to the tableData package variable.

FOREACH LOOP CONTAINER

Next, traverse the recordset with a loop and capture each column value. Here are the properties that you need to set:

• Set Enumerator to Foreach ADO Enumerator.

• Set the ADO object source variable to the tableData package variable, as shown in Figures 9 and 10.

• Finally, in the Variable Mappings, add the package variables postTime, user, and object, based on columns’ arrangement in the recordset.

DATA FLOW TASK

Inside the ForEach Loop Container, there is a Data Flow Task. It does all you need to do for each of the records in the recordset. Here, you can also tag the records as processed to avoid reprocessing (see Figure 6). The package variables will be updated as the loop progresses.

So, what do you think?

3. Make Users Run SSIS Packages by Using a Stored Procedure

The third and last option is using a stored procedure. It triggers the execution of a package. Instead of a package, you’re going to have a stored procedure example to present. It doesn’t matter what’s inside the package at this point.

You can use the same approach on other SSIS packages.

What You Need

• A SQL Server Agent job for the SSIS Catalog package with 1 step to execute the package. It does not require schedules.
• A stored procedure that will call msdb.dbo.sp_start_job to execute the job.
• The EXECUTE permissions for the user account that will execute the stored procedure.
• An app (whatever it is) that will trigger the execution of the stored procedure.
• An admin account to impersonate your non-admin user to execute the SSIS package.

Advantages

• No need to schedule a job for the SSIS package. Although, you need to create a job in SQL Server Agent.
• Any data source will do – SQL Server or any relational database, Excel, text file, Sharepoint list, etc.
• The timing of the execution is more flexible for your user.

Disadvantages

• The msdb stored procedure does not accept package parameters. If you need parameters, follow the 2^nd approach with a table of parameters required by the package.

Stored Procedure Example

This example shows the stored procedure having EXECUTE AS and REVERT to execute an SSIS package successfully.

The steps below assume you already have the SSIS Catalog Database (SSISDB), and you are familiar with it.

1. Create 2 Windows or domain accounts: User1 – the non-sysadmin user and ssis_user1 – the sysadmin user.
2. Using Visual Studio, create and deploy the SSIS package in SSISDB. We use PackageSample.dtsx as an example.
3. Create an SQL Server Agent job for the SSIS package. Note that you don’t need to add a schedule. We use testJob as an example.
4. Grant IMPERSONATE permissions. We need this to run msdb.dbo.sp_start_job that will execute the package successfully. Here’s an example:

USE master
GO
GRANT IMPERSONATE ON LOGIN::[DOMAIN1\ssis_user1] TO [user1]
GO

5. Create the stored procedure to run the package.

The stored procedure will use the EXECUTE AS to impersonate the sysadmin ssis_user1 until the package is triggered to run. REVERT will return the security context to the non-sysadmin user user1. The msdb stored procedure sp_start_job will activate the package to start. This is why sysadmin permission is needed:

CREATE PROCEDURE spRunJob
AS
BEGIN
    EXECUTE AS LOGIN = 'DOMAIN1\ssis_user1'           -- impersonation starts here
    EXEC msdb.dbo.sp_start_job @job_name = 'testJob'  -- execute the job
    REVERT                                            -- impersonation ends here
END
GO

6. Test the setup work. Run this in SQL Server Management Studio query window:

-- This assumes that you are logged-in to SSMS as an admin
-- Simulate running the package using user1 with reduced security
EXECUTE AS LOGIN = 'user1'    

-- this is for you to see if the context has shifted to user1
PRINT 'About to execute stored procedure by ' + SUSER_NAME()     

-- execute our sample stored procedure
EXEC spRunJob

-- return the security context to previous settings
REVERT

-- this is for you to see if the context has shifted back to you
PRINT 'After REVERT, the security context is back to ' + SUSER_NAME()

How about using the built-in stored procedures in SSISDB?

If you use the built-in SSISDB stored procedures to execute packages, the stored procedure will look like this:

CREATE PROCEDURE spRunPackage
(
     @fromDate DATE,
     @toDate DATE,
     @category VARCHAR(15)
)
AS
BEGIN
    DECLARE @execution_id bigint 
    EXECUTE AS LOGIN = 'DOMAIN1\ssis_user1'    -- impersonate

    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'PackageSample.dtsx'
                        ,@execution_id=@execution_id OUTPUT
                        ,@folder_name=N'Sample1'
                        ,@project_name=N'SSIS3'
                        ,@use32bitruntime=False
                        ,@reference_id=Null

    -- Define parameters 
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
                           ,@object_type=30
                           ,@parameter_name=N'fromDate'
                           ,@parameter_value=@fromDate 
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
                           ,@object_type=30
                           ,@parameter_name=N'toDate'
                           ,@parameter_value=@toDate 
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
                           ,@object_type=30
                           ,@parameter_name=N'category'
                           ,@parameter_value=@category  

    -- Execute the package
    EXEC [SSISDB].[catalog].[start_execution] @execution_id 

    -- Switch to previous security context
    REVERT
END
GO

Advantages

• No need to create a job in SQL Server Agent.
• You can pass parameters in the stored procedure, and then to the package, using [SSISDB].[catalog].[set_execution_parameter_value].

The Big Catch

I can’t make it work in SQL Server 2019, using a non-sysadmin user. However, I made it work before, using SQL Server 2012. The stored procedure works if there’s no impersonation only. It means no EXECUTE AS and REVERT and should be triggered only by a sysadmin user.

That sucks because it won’t serve our purpose. I tried to make it work but failed, as you can see in this forum thread. Eventually, you can only use the msdb stored procedure along with the SQL Server Agent job.

How About Using the DTExec Utility to Make Users Run SSIS Packages?

I haven’t tried this one because of our IT policy. Here’s our situation:

• Using dtexec as an option for users to run packages requires installation on their stations. This is not allowed.
• Even if we secretly install it on their stations, it will require us to give them permissions to the server to run it. Also, not allowed.
• We design our apps to have a middle-tier. The front-end app talks to this middle-tier, and the middle tier talks to the database server. This avoids giving access to users straight to the database.
• Last, we have users who are computer science graduates and attempt to tinker with these tools.

There are many articles about the dtexec utility and its usage for packages execution. Here’s an example that uses the Project Deployment Model and the SSIS catalog:

DTExec /ISSERVER "\SSISDB\folderB\Integration Services Project17\Package.dtsx" /SERVER "." /Envreference 2 /Par "$Project::ProjectParameter(Int32)";1 /Par "Parameter(Int32)";21 /Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb /Par "$ServerOption::SYNCHRONIZED(Boolean)";True

I cannot add any more information on this, but here’s a link from Microsoft discussing the installation, syntax, and examples. If you have tried this one with success, this can be the 4th option.

Conclusion

Our first approach to make users run SSIS packages is pretty straightforward. If you only need to extract a file, this is it. Let the file pull the trigger.

Meanwhile, the second approach is similar but more flexible in terms of what can be processed. Plus, you can accommodate more than one user for this.

But if your users need more flexibility in the timing, then a stored procedure with an EXECUTE AS, and REVERT will do the trick. Elevate to an admin privilege at the moment of execution and then revert it to a lower privilege. And let your app do the rest.

Have you thought of other ways to do it? You can share it in the comments section.

And if you like this post, why not spread the word on social media? Just click the social media buttons on this post.

Tags: database administration, sql server, ssis, stored procedure Last modified: September 20, 2021