How to Run SSIS Package With Multiple Parameters From a Batch File

Running an SSIS package with multiple parameters from a batch file might sound like a niche skill, but it’s a game-changer for anyone looking to streamline their workflows and enhance efficiency. This approach offers a simple yet powerful way to automate and control complex data integration processes without manually adjusting configurations every time. Whether you’re managing ETL workflows across multiple environments or handling dynamic datasets, this method allows for flexibility, scalability, and consistency, reducing the risk of human error and saving valuable time.

The real advantage lies in its practicality. By defining and passing parameters through a batch file, you can quickly adapt your SSIS packages to different scenarios, making them reusable and adaptable without modifying the package. It’s a cost-effective solution for teams looking to optimize operations without compromising on reliability or precision.

Curious about how to implement this approach and take your SSIS game to the next level? Keep reading to discover step-by-step insights on executing an SSIS package with multiple parameters from a batch file like a pro.

Table of contents

• Practical use case overview
• Preparation
• Setting up your SSIS environment
• Executing SSIS packages
• Automating execution with batch files
• Best practices
• Conclusion

Practical use case overview

Let’s imagine that you were assigned a project in which you need to create an SSIS package that should perform the following tasks:

• Extract the details of the female students who have enrolled throughout the year. The Source database is Oracle 21c, and the destination database is SQL Server 2022.
• The package should be executed on the last Monday of every year. Suppose for the year 2024, the package should be executed on 30^th December.

To do that, you will have to create:

• A package that uses multiple parameters. The first parameter is the gender of the student, and the second parameter holds the current year. 
• A batch file that contains a dtexec.exe command with /SET keyword. The variable for gender will hold a static value and a parameter that has a year will be dynamically changed as per the current year.
• Automate the batch file execution using a task scheduler.  

But first, let’s understand the basics of SQL Server integration services package and their usage specifics.

SSIS is a powerful tool that is used to create ETL packages. As the name suggests, the ETL packages extract, transform, and load the data between various databases and other files.  The key features of the SQL Server integration services are:

• Data extraction from various sources, like flat files, XML files, and other databases. It supports various destinations like SQL Server, oracle and flat file. 
• Execution of various tasks, like data cleaning, data aggregation, sorting, merging, and splitting.
• The usage of custom SQL and programming scripts for data transformation tasks.
• Support for various automation tasks, like backups, database maintenance, and file transfers. 
• A built-in error handling mechanism and debugging tools like data viewer, that helps view the ETL processes in real time. 

The SSIS packages can be developed within SQL Server data tools and Visual Studio.

From this article, you will learn how to execute an SSIS package using dtexec command-line utility. The dtexec is a command line tool that is used to execute any SQL Server integration services package. Check the basic syntax of dtexec command below:

dtexec /F [PackageFile] [/Option [Value]]...

The command supports various options. The following table shows the list of basic options that can be used in dtexec utility with simple explanation and examples.

Options	Description	Example command
/F	Specify the location of the SSIS package file.	dtexec /F “C:\MyPackage.dtsx”
/SQL	It loads the package that is stored in SQL Server. In SQL Server, the SSIS packages are stored in the MSDB database.	dtexec /SQL “\Folder\MyPackage.dtsx” /SERVER “MyServer”
/DTS	This option is used when you want to run the package that is in the SSIS package store.	dtexec /DTS “\Filesystem\MyPackage.dtsx”
/SET	Override the properties of the SSIS package during execution.	dtexec /F “C:\MyPackage.dtsx” /SET “\Package.Variables[MyVariable].Value”;”NewValue”
/PARAMETER	Assign values to the parameters declared in the package. The option is available after SQL Server 2012	dtexec /F “C:\MyPackage.dtsx” /PARAMETER “MyParameter”;”MyValue”
/SERVER	This option is used when you are running a package from integration services server (SSISDB). The path of package will be specified after /ISServer option.	dtexec /Server “SQLInstance” /ISServer “SSISDB\PackageFolder\Package.dtsx”
/REPORTING	This option is used to specify the logging options which are following: N (No reporting) E (Errors) W (Warnings) I (Informational messages) D (Data Flow task events) P (Progress)	dtexec /F “C:\MyPackage.dtsx” /REPORTING “E”
/LOG	This option is used to specify the logging option.	dtexec /F “C:\MyPackage.dtsx” /LOG “logprovider;logfile.txt”

Preparation

To reproduce this tutorial, let’s install Oracle 21c and create a database named SchoolDB. The process of creating a database in Oracle is different from the process of creating a database in SQL Server. You can read and article on Creating databases in Oracle to learn more about the process of creating such a database. 

Let’s imagine that our Oracle database has a table named tblStudentMaster. The table contains information about the students, such as their birthdate, gender, and school enrollment details, like their class, enrollment date, and other details. 

Now, create another database named StudentMasterDB in SQL Server. The database contains information about the students studying across the entire city. This database should have a table named Mst_tblStudents. Note that the table definitions of tblStudentMaster and Mst_tblStudents are the same. 

Next, create an SSIS project that contains an SSIS package that exports data from the Oracle database to the SQL Server database.

Setting up your SSIS environment

In this article, we are covering high-level information on how the connection managers, package variables, and data flow task works. We’re using SSIS Source Component for Oracle from Devart to connect and export the data from the Oracle database and Devart SQL Destination Component to connect and import data from the Oracle database.

To start, configure the Oracle connection manager with the settings shown in the screenshot below.

The destination should be an SQL Server database. Configure the Devart SQL Server connection manager with the settings shown in the screenshot below.

As per business requirements, let’s create two variables named varGender and varEnrollmentYear to store the values of gender and enrollment year. The data type of varGender is String, and varEnrollmentYear is Int32.

Assign values to both parameters. The value of varGender is F, and varEnrollmentYear is going to be 2024. Both parameters are mandatory; hence let’s set the values of the Required column to Yes. The scope of the variable is going to be Package.

Here is the screenshot of the variables section of the SSIS package designer.

We are exporting data based on the values passed in EnrollmentYear and Gender variables. Hence, the query in the text box should be written as follows:

SELECT 
	FirstName, LastName, DateOfBirth, Gender, 
	EnrollmentDate, Class, ContactNumber, 
	Address, IsActive
	FROM SCHOOL_ADMIN.TBLSTUDENTMASTER t
WHERE 
Gender = '<@User::varGender>'
AND
TO_CHAR(t.EnrollmentDate, 'YYYY') = '<@User::varEnrollmentYear>'

Here is the screenshot of the connection manager.

In the SQL Server destination editor, we must select the appropriate connection manager, which is Devart SQL Server Connection Manager, select the desired destination table, and finally map the source and destination columns. 

Here are the screenshots.

Fig 1: Select connection manager.

Fig 2: Select destination table

Fig 3: Column mapping of source and destination tables.

The Data Flow task should look like in the image below.

Now, let’s execute the package.

Executing SSIS packages

We can execute the SSIS package using Visual Studio and dtexec.exe command line tool. To run the package in Visual Studio, click Start button in the menu bar. 

In our demo, we will use dtexec.exe utility to execute the SSIS package. The syntax to execute this SSIS package with multiple parameters will be as follows:

dtexec /F "..\MyPackage.dtsx" 
/SET "\Package.Variables[variable_1].Value";"value_1" 
/SET "\Package.Variables[variable_2].Value";"value_2"

Here are some insights on what variables and variable names you should use.

Varilable_1	Specify the varilable name that we have created in a package.
Value_1	Specify the value that you want to use to execute the package.

Note that to pass multiple parameters, we must specify the /SET keyword multiple times.

In this demo, we want to export the records of the female students who have enrolled in 2024. To do that, the dtexec command will be as follows:

dtexec /F "..\Package.dtsx"
/SET "\Package.Variables[varGender].Value";"F"  
/SET "\Package.Variables[varEnrollmentYear].Value";"2024"

In this command, we are assigning the values to the variables using /SET option. As per requirement, we must specify gender in varGender and enrollment year of student in varEnrollmentYear. Hence, we must use the /SET option multiple times. 

To run the command, open the command prompt and paste the command mentioned above.

The package execution will start.

Once the package executes successfully, the records will be transferred to the SQL Server database. To verify, open SQL Server management studio, connect to SQL Server instance, and run the following query:

USE [StudentMasterDB]
GO
SELECT * FROM Mst_tblStudents ms

Here’s an output you can expect.

As you can see, the data of female students enrolled in 2024 has been exported to SQL Server successfully. 

Now, let’ us’s understand how to automate the export process using Windows task scheduler.

Automating execution with batch files

We are going to use the Windows task scheduler to automate the package execution. Check the business requirements of automation below:

• The script should run on the last Monday of the year.
• The script should export the data of current year.

E.g., if the script executes on 27th December, 2024, it should export the data of female students enrolled in 2024.

Based on this requirement, we must dynamically change the value of varEnrollmentYear parameter in dtexec command. 

@echo off
:: Get the current year
for /f "tokens=2 delims==." %%A in ('"wmic os get localdatetime /value | findstr /r ^LocalDateTime"') do set CurrentYear=%%A
set CurrentYear=%CurrentYear:~0,4%


:: Define the SSIS package path
set PackagePath=" ..\Package.dtsx"


:: Define the value for varGender
set varGenderValue="F"


:: Execute the SSIS package with dynamic variables
dtexec /F %PackagePath% ^
/SET "\Package.Variables[varGender].Value";%varGenderValue% ^
/SET "\Package.Variables[varEnrollmentYear].Value";%CurrentYear%

In the script, we are going to use:

1. wmic os get localdatetime function to get the current date and time in YYYYMMDDHHMMSS format. 
2. /f to get the first 4 characters of the date and store it in CurrentYear variable.
3. the value of varGenderValue parameter is static and set to “F”.

Save the code in a batch file named ExportStudentEnrollment.bat. Now, let’s create a task in the task scheduler.

 Open Windows Task Scheduler and select Create Basic Task from Actions tab. 

Once Basic Task wizard starts, in the first screen, enter the desired task name and description. Click Next.

In the Task Trigger screen, we will specify when the task will be triggered. As per business requirements, the task should run on the last Monday of December. Thus, select Monthly. Click Next.

In the Monthly screen, select December from the Month drop down box, Last and Monday from the dropdown menu. Click Next.

In the Action screen, select the task that you want to perform. We want to run a batch file, hence select Start a Program. Click Next.

In the Start a Program screen, enter the location of the batch file that we have created. 

In the summary screen, you can view all the configurations. Click Finish to create the task and close the wizard.

Once the task is configured successfully, you can view it in the task scheduler library. Here is the screenshot for reference.

Now, let us explore the best practices we can follow while using parameters. 

Best practices

Here are some best practices that you can implement while using SSIS packages

1. The SQL Server integration services have excellent error handling and logging mechanisms. You can always log critical events like OnTaskFailed, OnError, and OnWarning at the package level.
2. You can use the data viewer in the SSIS package to check whether data is being extracted properly or not.
3. Always mark the passwords and connection strings as Sensitive parameters in SSIS. The parameters marked as sensitive will automatically get encrypted based on the package protection level.
4. Set the package protection level appropriately. The SSIS has different protection levels, such as
   1. Don’t Save sensitive
   2. Use Encrypt sensitive with user key, Encrypt sensitive with password, Encrypt all with Password, and Encrypt all with user key protection levels.
   3. Ensure you have enough server storage.
5. If you are using the configuration files (.dtsConfig), make sure the configuration file is stored in a secure location with restricted access.
6. Always encrypt the SSISDB catalog database with Transparent database encryption.
7. The SSIS packages developed using the Visual Studio or SQL Server data tools can be integrated with the version control tools like GIT. The .dtsx, .dtproj, and additional files can be tracked using version control tools.
8. The version control is very crucial because it helps manage and track the changes made in an SSIS project or package. 

Conclusion

From this article, you have learned how to execute an SSIS package using the command line utility. For demonstration, we created an SSIS package that exports the data from the Oracle database to the SQL Server database. We used the Devart Oracle source and Devart SQL Server destination components to streamline data, and you are free to try them out for your projects.