ustcer: How to use a variable inside SQL in SSIS Data Flow Tasks

Sometimes you need to use a variable inside SQL in SSIS Data Flow Tasks. For example, you may want to select TOP x records in a periodically scheduled task. This X variable could be used to adjust performance or sometimes a condition cannot be specified when the package is created. And of course there are many other reasons for why you would want to use a variable in Data Flow Tasks. The problem is that, although you can put "?" in your SQL query for the DB source, you cannot set up the parameters there by clicking a button. And you will get an error message if you try to use this query. But there are ways to accomplish the same thing. In this article I will show you how to do it using Script Task.

In this tutorial I will use the AdventureWorks database. Let's say we want to copy TOP x records from the table [AdventureWorks].[Person].[Contact], but we want to specify this X value by using a variable. We want to copy data to an example table and we need only ID, FirstName and Lastname.

If SSIS worked as we would expect, we should just create a Data Flow Task containing an OLE DB Source item withSQL code like this:

SELECT TOP ? [ContactID]
,[FirstName]
,[LastName]
FROM [AdventureWorks].[Person].[Contact]

This works for SQL task control flow, but when you try to click on the Parameters button in OLE DB Source you will get this error:

The only way to use a variable in SSIS is using SQL command from variable. Here I will present to you the first way you can use it.

SQL command variable from Script Task

Step 1 - Create variables

The first thing we must do is create new variables. For our example we will need two. The first is SizeVariable and the second is SQLCommand. We will use the first one to define the X value in our TOP x command and the second will contain the whole SQL query for the OLE DB Source.

The variable for the SQL command needs to be a string. For the SQLCommand default value, insert this:

SELECT TOP 1 [ContactID], [FirstName], [LastName] 
FROM [AdventureWorks].[Person].[Contact]

It will help you to create OLE DB source properly with the correct columns.

Step 2 - Create destination table

For the purposes of this tutorial we need a destination for the data. I will create a SQL Task with the following SQL command:

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[Example_VariableInDataFlowTask]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.[Example_VariableInDataFlowTask]
GO
CREATE TABLE dbo.[Example_VariableInDataFlowTask](
[ContactID] [int],
[FirstName] nvarchar(50) NOT NULL,
[LastName] nvarchar(100) NOT NULL
) ON [PRIMARY]

On each run it will create the destination table. If the destination table already exists, then it will first drop the table.

variable-in-data-flow-task-create-destination-table.jpg

Step 3 - Script task

This is the main part. We need to create and save SQL to the SQLCommand variable. First create a Script task and open it.

Select ‘Script' from the list on the left.

Here you need to enter SizeVariable for the ReadOnlyVariables option and SQLCommand for theReadWriteVariables option.

variable-in-data-flow-task-script-task-open-with-variables.jpg

Now click on the Design Script... button. Microsoft Visual Studio for Applications will open.

We need to edit the Main() function. For our example I will insert the following:

Dts.Variables("SQLCommand").Value = "SELECT TOP " & _
Dts.Variables("SizeVariable").Value.ToString & _
" [ContactID], [FirstName], [LastName] FROM [AdventureWorks].[Person].[Contact]"

What is happening here? If you know a little Visual Basic then it's easy for you to understand already. I am setting the value of the SQLCommand variable to be the concatenation of three strings:

"SELECT TOP "
Dts.Variables("SizeVariable").Value.ToString
" [ContactID], [FirstName], [LastName] FROM [AdventureWorks].[Person].[Contact]"

Don't forget to convert the SizeVaraible (or your other variables) to a string.

Now we can save it in Microsoft Visual Studio for Applications and close Visual Studio. Press OK in the opened Script Task.

Step 4 - Data Flow Task with Variable

Now we can finally create the Data Flow Task.

For the OLE DB Source in the Data Flow Task we need to set Data access mode to ‘SQL command from variable' and select the SQLCommand variable.

For the destination we can select our table Example_VariableInDataFlowTask. Of course we need to create it first. Use the SQL command above that we used for the SQL Task. Then choose the destination table and mappings.

We are done. By setting SizeVariable you can now define how many records you will get in the Example_VariableInDataFlowTask table when the package is run.