How to apply dynamic SQL Query on OLEDB Command in SSIS Package

We can apply the dynamic SQL query in OLEDB command task by two different methods in SSIS package. In this post, we are going to see how we can utilize OLEDB command task for dynamic SQL query by following steps.

For Instance, We have to load employee flat file data to SQL table and need to perform INSERT/UPDATE operation based on the employee_id availability in destination table
How to use dynamic SQL query on OLEDB Command

Primary steps in SSIS package

• Create Integration Service project in Visual studio.
• Create OLEDB connection and Flat file connection in Connection Manager Properties.
• Drag and drop the Data Flow Task on Control flow and define the flat file data source inside the Data Flow Task.
• Use Look up transformation to check employee data availability on destination table.
• Ensure all the tasks defined properly in Data Flow Task before going to use OLEDB Command and now,

Step 1: Drag and drop the OLEDB Command task and go to Advanced Editor by using double click or edit the task.

Step 2: Choose the OLEDB connection name in connection manager tab which we configured in connection managers.

Step 3: Paste below mentioned dynamic SQL query in SQL command field in Component properties tab.

Method 1:

We can directly use the dynamic SQL query in OLEDB command as below.

Dynamic SQL Query:

DECLARE @Employee_Name VARCHAR(50)
DECLARE @Employee_Address VARCHAR(50)
DECLARE @Employee_City VARCHAR(50)
DECLARE @Employee_State VARCHAR(30)
DECLARE @Employee_Salary INT
DECLARE @Employee_ID INT
DECLARE @SQL_Query NVARCHAR(4000)

SET @Employee_Name = ?
SET @Employee_Address = ?
SET @Employee_City = ?
SET @Employee_State = ?
SET @Employee_Salary = ?
SET @Employee_ID = ? 

SET @SQL_Query = 'UPDATE Employee SET Employee_Name = @Employee_Name, Employee_Address = @Employee_Address, Employee_City = @Employee_City,
Employee_State = @Employee_State, Employee_Salary = @Employee_Salary
WHERE Employee_ID = @Employee_ID'

EXEC sp_executesql @SQL_Query, N'@Employee_Name VARCHAR(50), @Employee_Address VARCHAR(50), @Employee_City VARCHAR(50), @Employee_State VARCHAR(30),
@Employee_Salary INT, @Employee_ID INT', @Employee_Name, @Employee_Address, @Employee_City, @Employee_State, @Employee_Salary, @Employee_ID WITH RESULT SETS NONE;

Method 2:

We can create a stored procedure in SQL database and use the same dynamic query itself. We can call the stored procedure in OLEDB command as below.

Update using Stored Procedure:

DECLARE @Employee_Name VARCHAR(50)
DECLARE @Employee_Address VARCHAR(50)
DECLARE @Employee_City VARCHAR(50)
DECLARE @Employee_State VARCHAR(30)
DECLARE @Employee_Salary INT
DECLARE @Employee_ID INT
DECLARE @SQL_Query NVARCHAR(4000)

SET @Employee_Name = ?
SET @Employee_Address = ?
SET @Employee_City = ?
SET @Employee_State = ?
SET @Employee_Salary = ?
SET @Employee_ID = ? 

EXECUTE usp_UpdateEmployee @Employee_Name, @Employee_Address, @Employee_City, @Employee_State, @Employee_Salary, @Employee_ID

Step 3: Map the columns between Available input columns and Available Destination Columns. The available destination columns will be generated based on the question mark in dynamic SQL query.

Step 4: After completing the columns mapping in OLEDB command, we need to assign the appropriate data type for each field in Input and Output properties tab like as picture below.

Step 5: Once completed all mapping and settings in OLEDB command, we can execute the SSIS package and verify the number of rows affected on each tasks.

Also see below articles

Implement Best Practice and Performance tuning tips in SSIS and ETL Development
Top 15 Power BI interview Questions with detailed Answers