This write-up is part 2 continuation of the SSIS learning where we will discuss Expressions, Parameters explained by one of the student who attended MSBI training at Mumbai.
What do we mean Expressions?
- Expressions help us to set the values to the properties of SSIS component from Variables & Parameters.
For Configuring Variables in our Data Flow task where we have our ETL activity we have to use Expressions for it.
In order to set properties do the follow below steps: -
On Data Flow, do a right click on “CSV Connection” connection manager and click on “Properties”
Once the Properties window is open then click on Expressions editor to set it as shown in the image below.
Once Property Expressions Editor window is open set Property as “ConnectionString” and Expression.
Thereafter open Expression Builder, then drag and drop variable on Expression field as shown below. Then click OK to close Expression Builder window.
Followed by OK to close Expression Editor Window.
What are Parameters?
- Parameters are used when we want to deploy our application in production where the files will change, folders will change, and database connections will change.
- So we need parameters from outside so that we can provide and set the values internally.
And to configure parameter we will be using the Parameter tab provided in our package.
Now we will add the parameter,
Now to configure this parameter in our Foreach loop, open Editor window click on Collection to the left and open Expression Editor window for creating expression
Here set Property > Directory and then click on Expression to open Expression Builder window as shown in the image down below
Once window is open drag and drop parameters on Expression and click on OK to close the Expression Builder window.
Then click on OK to close the Editor window.
What is the difference between Variables & Parameters?
Variables: They are more internal to the SSIS application. They are used when we want to pass data from Control Flow to Data Flow.
Parameters: They are more external to the SSIS application. They are used when we want to deploy our application, they are set from outside.
Do excuse us in case things which were not understood after all it is written by student and shared his learning during
2 days MSBI course.