How To Run Dynamic T-SQL In SSIS OLE DB Command

Using dynamic t-sql is one of the good abilities in SQL Server, and there are times which you need to do this in SSIS.

Suppose you have a data stream in SSIS Data flow task and you want to run a dynamic t-sql query per each data row values, probably your first stop is OLE DB Command to run the sql statement per each data row, but OLE DB Command has a problem with dynamic statements, So you should use other ways,

In this post I’ll describe the problem of OLE DB Command with dynamic t-sql, and propose two solution to do this with other transformations or tasks.

First of all let’s explain the scenario exactly,

Scenario:

Suppose you have a data stream with some data rows, for example these data rows:

and suppose you have a stored procedure as below:

create PROCEDURE [dbo].[AddColumnToTable]
    @TableName varchar(max),
    @ViewName varchar(max)
AS
DECLARE     @tsql varchar(max)

END

In this procedure a varchar variable will generate a dynamic alter sql command and then run it with exec, so this is dynamic t-sql. and this will add a new column for each [Name] Column per data flow rows, Don’t ask me what is usage of such thing, this is just a sample of dynamic t-sql for showing how you can work with it, don’t think about logic now!

So suppose you want to add a column per each [Name] in data stream to the [log] table in sql server database. [log] table is a simple database table with this structure:

After defining the scenario, your first stop to do this will be OLE DB Command probably because OLE DB Command designed to run sql statements per each data row in data flow,

If you add an oledb command and write this query in sql statement property of it:

exec [dbo].[AddColumnToTable] ‘[log]’,?

Now whenever you go to the Column Mappings tab of OLE DB Command , you will get an error:

Error at Data Flow Task1 [ OLE DB Command [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Incorrect syntax near ‘0’.".
Error at Data Flow Task1 [OLE DB Command[1]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

So as you see the OLE DB Command has problem with dynamic t-sql,

So I propose two Solution in this case to do run dynamic t-sql in SSIS:

Solutions:

First Solution: Using Script Component as Destination

In this solution you can add a script component instead of OLE DB Command to run the dynamic t-sql.

So add a script component as destination,

and check the [Name] column in the input columns

and add a connection manager in connection managers tab ( I used ADO.NET connection manager here because I like to use SqlConnection object in .NET )

Now go to script tab, and click on Edit Script ( in this sample I used SSIS 2008 which accept C# language ),

and write this code in the Input0_ProcessInputRow method to run the stored procedure:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        System.Data.SqlClient.SqlConnection Conn = (System.Data.SqlClient.SqlConnection)Connections.OLEDBConnection.AcquireConnection(null);
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("exec [dbo].[AddColumnToTable] ‘[log]’,’" + Row.Name + "’", Conn);
        cmd.ExecuteNonQuery();       
    }

This code will acquire the connection manager first and then convert it to System.Data.SqlClient.SqlConnection  object, and then run a SqlCommand with it, the [Name] column set as input parameter to the sql statement.

Now save the script and Click OK, and run the package, everything will work fine.

this is the data flow schema:

and this is results in [log] table:

Second Solution: Using Object Variable and run t-sql with Execute SQL Task

The Execute SQL Task hasn’t any problem with dynamic t-sql in the other hand. so you can use it as a solution, but as you want to run this statement based on all records in data stream, you should load data stream into an Object type variable with a RecordSet Destination , and then loop through the object variable with Foreach loop, and then execute command in Execute sql task.

Add a package scope variable of Object type, and name it as DataStream .

Add another package scope variable of string type, and name it as Name.

Now add a RecordSet Destination to the data flow and set variable as DataStream there

and in the Input Column tab, check the Name column

this is full schema of Data flow:

Now go to Control Flow , and add a Foreach Loop Container after the Data flow task,

In the Foreach Loop Container, set enumerator as ADO Enumerator, and set source variable as DataStream

then in variable mappings tab, add Name variable with index 0

Click OK, now add an execute sql task inside foreach loop container, and double click on it,

set the OLEDB Connection there, and write sql statement in the SQLstatement property:

exec [dbo].[AddColumnToTable] ‘[log]’,?

Then go to parameter mappings tab, and add a parameter with variable as Name, and parameter name as 0 ( this is based on OLEDB Connection manager)

Now run the package, this is schema of Control Flow:

after running the package, columns will be add to the [log] table as below:

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.