3 Easy and Secure Ways to Make Users Run SSIS Packages

1. Make Users Run SSIS Packages Using a File

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

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

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

  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.

PRECEDENCE CONSTRAINTS

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

DATA FLOW TASK

SCRIPT TASK — Move the processed file to the Processed folder

SEND EMAIL TASK

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

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. The second SSIS package example that checks for data in a table before proceeding to process.

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

DATA FLOW TASK — Get Table Data

Figure 7. Retrieving data from the SQL table to the recordset.
  • 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.

FOREACH LOOP CONTAINER

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

DATA FLOW TASK

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

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 2nd approach with a table of parameters required by the package.

Stored Procedure Example

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

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
{coding}Sight

{coding}Sight

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