Expression indicator SSIS

As we all are aware with expression usage in SSIS.
Lets see an example of using connector  for creating dynamic connection.
We can create expression for connection string and assign it to connections.
So at rutime connection string from this  expressions are evaluated first and based on it connection is made.
How SSIS does not giving any indication either connection has expressions defined not.

But from SQL 2012 we can see icon fro expression near the connection on which we have defined expression

see the example below
Here we defined expression for the connection so that we can see fx symbol near the connection

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

use procedure as input in dataflow task

If we use procedure as  source in data flow task , this generally dont work in SSIS
so we have to pass

SET nocount OFF

SET fmtonly OFF

 before running the procedure

So sql command will be like this

SET nocount OFF

SET fmtonly OFF

EXEC Proc1

cheers 🙂