How to pass variable as a parameter in Execute SQL Task SSIS?

sql

parameterization

sql-queries

dynamic-sql

byNikita Barsukov·Feb 19, 2025

In order to execute a parameterized query in SSIS, you have to:

Establish a SSIS variable.
Navigate to the Execute SQL Task, locate Parameter Mapping tab:
- Choose Variable Name (for example, User::MyVariable),
- Select the correct Data Type (for instance, Long),
- Enter the Parameter Name (0 for connections using OLE DB, @ParamName for the rest),
- Fill in the Parameter Size (if relevant).

Here's a sample query which makes use of ? for OLE DB and named parameters:

UPDATE MyTable SET ColumnValue = ? WHERE ID = @ID

Your Parameter mapping for OLE DB should be set up like this:

Variable Name     Data Type     Parameter Name
User::MyVariable  Long          0
User::ID          Long          @ID

It's got to be ensured that the data types align correctly and parameters are appropriately named to prevent any errors and to ensure a smooth execution.

Digging deeper: Understanding Parameterization

Getting into the core of the topic, parameterized queries are a potent strategy that can help enhance security and feed performance during SQL operations. Here goes a small cheat sheet for different connection types:

ADO.NET: Use named parameters (e.g. @paramName) straight in your SQL queries.
OLEDB: Take ? as placeholders and reference parameters with a zero-based index.
ODBC: Parameters are refereed by a one-based index.

Just a heads up - make sure the parameters in SSIS are ordered exactly as they are inside the stored procedure or SQL query.

Cooking up Dynamic SQL Scripts

Creating dynamic SQL within SSIS Execute SQL Task isn't just about dumping variables anywhere; it's more or less equivalent to mastering the art of syntax and structure:

Declare variables inside the SQL snippets to effectively manage dynamic inputs.
Dynamically preset variable values prior to the task with the help of control flow elements.
You've heard of Karma right? Well, commenting your SQL code might make your future self thank you later!
Default values could be your savior when it comes to handling optional parameters in SQL.

Practicing these things will put you on the fast-track to building professional, scalable, and maintainable SSIS packages.

Making your SQL scripts more adaptable

Your SQL commands can morph to suit various situations with a few tricks up your sleeve:

Parameterize dynamic conditions in WHERE clauses to make your SQL scripts more flexible.
Build INSERT, UPDATE, and DELETE statements with parameter markers; this allows you to perform various database operations using a single task.
Data Type Congruency: Sync up the data types of SSIS variables and SQL parameters to prevent issues due to type casting.

Embrace the dynamic nature of SQL for better reusability and simplicity.

explain-codes / Sql / How to pass variable as a parameter in Execute SQL Task SSIS?

Linked

Can parameterized statement stop all SQL injection?



Exec failed because the name not a valid identifier?



Declare Variable for a Query String



Sql Server procedure declare a list



Parameterize an SQL IN clause