Expression Task in SSIS 2012

As we all are aware with the variables in SSIS, which is used  for holding custom values and assign to various properties of objects at run time. If  we want to assign custom values to variables at runtime we have  have to create an script object to assign value to variable.

Now from SQL 2012 we can assign value to Expression using Expression Task. We can add this task to workflow and assign value to variable using SQL Server Integration Services Expression Language.

Lets see how to do it

Lets see we have a variable named test in the package.
Its assigned value is “a” in the variable window.

Now if we want to assign values of current year to this variable at runtime
We will add Expression Task above the Execute SQL Task and assign value to the variable

Now we will assign expression to variable
Double click on expression task and assign expression to it

Now lets execute the package
We will add breakpoint to Execute SQL Task to check the value of variable and enable watch to get the value of variables

As we can see new value of current year is assigned to variable using the expression.
This can be an easy way to assign value to variables compare to script task as well as easy to debug application by assigning custom values to variables

Row count int sys.dm_exec_query_stats

SQL 2012 has added 4 new columns 

total_rows,

last_rows,

min_rows,

max_rows  in sys.dm_exec_query_stats DMV

This will help us to find the queries which are returning large number of results and we can optimized them 

SELECT QS.*,

       Substring(ST.text,

                        ( QS.statement_start_offset /2 ) + 1,

                        (( CASE statement_end_offset

                           WHEN –1 THEN Datalength(st.text)

                                                      ELSE QS.statement_end_offset

                           END – QS.statement_start_offset )/ 2 ) + 1) AS statement_text

FROM   sys.dm_exec_query_statsAS QS

       CROSS APPLY sys.Dm_exec_sql_text(QS.sql_handle) AS ST

ORDER  BY max_rows DESC

DMV to get SQL Server Registry information

SQL Server 2012 has new DMV sys.dm_server_srvices
This provides details in registry related to SQL server

This provides some interesting information like 
SQL Server Version/Startup Parameters/Ports/Service startup options

SELECT *

FROM   sys.dm_server_registry

Easy way to get status of SQL Server Services in SQL 2012

SQL 2012 has some new DMVs.
One of them is sys.dm_server_services
It gives us details about the SQL Server services (SQL Serve,SQL Server Agent, Full Text)

SELECT *

FROM   sys.dm_server_services