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

FileStream Part-3

You can access first two parts from here

Part-1
Part-2

Lets see now  how to use FileStream to access files.

USE FileTest

CREATE TABLE FileTestTable  AS FILETABLE

This will create table with necessary fileds for file properties

Lets see the structure of this table

Next step will be to copy files to the shared location and access it using this table

• We will go to windows share which we configured in part-1 for Server at location \\Server\MSSQLSERVER

• Here we will get Directory which we configured for database in part-2 

• Here we will get directory for the table which we created

• Copy files this location    \\server\MSSQLSERVER\FileTableExampleDir\FileTestTable

   We will copy a file here

Now query the table

SELECT *

FROM   FileTestTable

As we can see we get details for the files from the table

Ok If we delete the record from table what will happen?
It will delete file from that  location also.

So it means we can manage windows files by the table in SQL Server itself 🙂

  

FileTable Part-2

Now we are at part2 of FileTable .

You can go for part1 from here

In Part1 We learn how to configure server to use of FileSteam.

Now we will create a database where we will use FileTable

•  Lets create a database named FileTest
• Go to property of FileTest Database

1.Set FILESTREAM Directory Name to FileTableExampleDir
2.Set access to Full/Read-Only
In this example we are going to set it FULL

Now we have enabled database for filestream access.

• Now we need to add filegroup for filestream access.

       Go to property of Database – > FileGroups

   Now add a file to file group fileFG

       Make This File Group as default

FileTable – Part1

FileTables are used to store this unstrucutred data.
FileTables contains FileStream which is used to store unstructured data in Database.

We can indexes/triggers/constraints on FileTable , But we can not drip constraints as well as system constraints from the Table
File Tables have fixed Schemas.
We can handle files like we are managing Files in Windows
We can copy/delete/insert files like we are doing in Windows from SQL Server

To use FileTables we need to configure server to use it.
Lets see how to do it

1.First we will enable FileStream on SQL Server
Go to SQL Server Configuration Manager
Select SQL Server Service and Right Click it

select Enable Filestream for Transact SQL access
Enable Filestream for I/O access.

Also specify windows share where we want to store files.
Here we specify windows share named MSSQLSERVER

2.Now configure server to access filestream

Sp_configure ‘filestream_access_level’,2

RECONFIGURE WITH override

 

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