Monday 9 May 2016

Expression task in SSIS

In previous versions of SSIS, if we wanted to manipulate SSIS variables in a Control Flow, we would need to use a script task.  This required some .NET programming knowledge.  New version of SSDT a new task is introduce, known as Expression task. With the help of this task we can evaluate the expression dynamically in control flow.
  
Here I am taking simple example. I have a package and I want to store the package name and when it gets executed in the database. With the help of Expression Task I am evaluating some expression and storing it on the table.

Let’s see the example


Here I am taking two Expression Task
First for getting the execution time of the package and second one for getting the current package name.
I am taking to variable.
Package name and Package description
  
Now we need to set customize dynamic value to the variable using Expression task.
Double click on the Expression task.
   
Set the value and evaluate the expression.
Similarly we need to set the values of the package name.
   
Now we get the both value. We need to insert these values in the table. For inserting the values in a table I am using Execute SQL task.
Table structure
create table PackageInfo
(
ID int identity(1,1),
PackageName varchar(100),
PackageDesc Varchar(500)
)

Taking Execute SQL Task
  
Creating the connection
  
Providing the SQL statement.
Mapping the parameter
  
Click ok.
Package is ready to execute.
  
Package executed successfully.
Let’s see the result in table



1 comment:

If you have any doubt, please let me know.

Popular Posts