3 Easy and Secure Ways to Make Users Run SSIS Packages

9 min readDec 28, 2020

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.


  • 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.


  • 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:

Figure 1: An SSIS sample package to process the file for extraction.

The SSIS Package should have:


  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).


  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.
Figure 2: Script Task Properties. Note the read-only and read-write variables.
Figure 3. The actual script in C# for the Script Task. Note the additional namespace and the if conditional block.


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:

Figure 4. Precedence Constraint properties if the file exists.

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

Figure 5. Precedence Constraint properties if the file does not exist.


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.


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.


  • 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.


  • 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.

Figure 6. The second SSIS package example that checks for data in a table before proceeding to process.

The package should include:


  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.


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:

Figure 7. Retrieving data from the SQL table to the recordset.

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.
Figure 8. Including the three needed columns in the recordset.
  • Set the recordset to the tableData package variable.
Figure 9. Setting the recordset variable to tableData.


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.
Figure 10. Setting the Foreach loop container.
  • 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.
Figure 11. Setting the Variable Mappings based on the exact column arrangement of the recordset.


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.


  • 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.


  • The msdb stored procedure does not accept package parameters. If you need parameters, follow the 2nd 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

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:

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

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
-- 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
-- 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:

@fromDate DATE,
@toDate DATE,
@category VARCHAR(15)
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
-- Define parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id
-- Switch to previous security context


  • 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.

Continue reading at 👉 https://codingsight.com/3-easy-and-secure-ways-to-make-users-run-ssis-packages/.

Join a community of database specialists. Weekly CodingSight tips right in your inbox. ✔ No spam ✔ 100% great content, always.

Subscribe here to get more industry insights👋




Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/