SSIS Expressions

1. Parameterized Connection

2. Precedence Constraint Expression

3. Execute SQL Task Expression

Parameterized Connection

I want to create a connection manager on the project level. This guy will be available for all my packages.

1. Right click on the Connection Managers area

2. Choose connection you need.

1. copy the connection string

2. go to Parameters section

use the connection string as a value for the parameter

Right-click on the connection manager > parameterize ...

1. In the Property drop box are listed all the Connection Manager properties. Find ConnectionString property.

2. Use existing parameter: use the project parameter. Confirm with OK

This guy already contains server name and database name. We may need a table to connect to the data. We can create another project parameter containing the table name.

I have created parameter Table; value is a table name from the database.

In Data access mode: Table name or view name variable

There is one more option the called: SQL command from variable.

You create a variable holding some SQL command

You can use that variable in the Data Flow.

Data access mode: SQL command from variable

Precedence Constraint Expression 1.1

You can create a package with multiple data flows and each package will run if the expression returns TRUE or something else.

You can create a data flow that will execute only on certain days. Other days will execute the second package.

Evaluation operation: Expression

Expression: click on the dots to build the expression.

DATEPART( "dw",GETDATE() ) ==2 (2: Tuesday)

Precedence Constraint Expression 1.2

The data flow task will execute depends on if there are some data in DimChannel table or not. You can use this logic for initial and incremental data loading.

Configuring Execute SQL Task

ResultSet : Single row

This will return an integer. We need to catch it into a variable.

Go to Result Set and create an integer variable.

When is created you can find it in the variable section.

set up the expressions for both Precedence Constraints...

It works. I don't want to truncate this tabla and show how it behaves when its empty. It is obvious without demonstrating truncation as well.

Execute SQL Task Expression

Last boring example I will share is how to use expression in Execute SQL Task.

I have created a table which will hold server name and the transaction data.

First create a connection to the server. You can copy the value, place it as a value in a variable, go to Expressions and use Connection property. I am not going to do it. I want to use property: SQLStatement Source to place an insert statement in that.

Go to Expressions > Expressions, click on the dots and this Property Expressions Editor will pop up.

Find SqlStatementSource. We must assign an expression to it.

" insert into Test(ServerName)

select 'This is my static value'

UNION

select ' " + @[User::ServerName] + " ' "

...build the expression. ServerName variable is hardcoded.

we got this fx symbol meaning that we are using an expression for some of its properties.

Build the data flow task, right now we have no data in the source table but Execute Sql Task will generate an insert statement so we will get some.

I repeatedly loaded the data, inserted by Execute SQL Task...