Data Flow Task - Sql Command From Variable

Normally in Data Flow Task we directly retrieve data from sql tables. But if we face a situation like we don't want all the data in the table but some conditional data. Here we can use two Data Flow Task methods, Sql Command method and Sql Command From a Variable method.

We can use sql command method for directly execute a sql command. But if we use say, a variable that set the value run time sql command from variable is the best method to use.


I'm going to demonstrate how to do the method 'Sql Command From Variable'. Here i'm going to do is get the date and holiday from the holidays table for current month (where its month id equal to current month id).


I drag and drop two Script Tasks and one Execute Sql Task.Then I create four variables, Month, MonthId, Year and SqlQuerry and I give a value to the SqlQuerry variable (This value will be replaced in the run time).

In the Control Flow first I set the current month and the current year for the Month and Year variables using one Script Task. Then I get the current month id from my dbo.Month table using the Execute Sql Task and then I set the variable SqlQuerry in the run time using other Sql Task.

This is how I set the SqlQuerry from Sql Task.

Now I drag and drop a Data Flow Task and in the Data Flow I drag and drop Ole Db Source, Data Conversion and Excel Destination.


In the Ole Db Source I create a connection from ole connection manager and set Sql Command From Variable for Data Access Mode.

And set the variable name as our variable name, SqlQuerry.

Then we can see the output columns from the Columns tag.

 

Then connect the source to the Data Conversion task and convert the 'Description' column to Unicode String (because the default type String does not match to the destination column type). Then create an excel file for the destination.

And create the Excel Destination and connect the Data Conversion task to the Excel Destination.

Finally execute the package and we can see the result.

This is the result.