Triggering a SSIS package by placing a file in a particular location

In SQL server 2012 you can trigger a SSIS package by placing a file in a particular location. This can be done by using a File table feature present in SQL Server 2012. This is a new feature introduced in SQL Server 2012.

File Table is a special table where you can store a file in SQL Server but the files can be accessed as if it is stored inside file system.

When a file is placed inside the File table location a record get inserted inside the File table. You can have after insert trigger over the File table and the trigger intern execute the package.

Enable File Steam feature inside the Server:

Step1: Execute the following query:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

Step2: Open the Sql Server Configuration Manager and go to the properties of the SQL Server. Open the FileStream tab.

Enable File Stream for the database

Step1: Run the following code for your database:

ALTER DATABASE database_name

SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' )

Step2: Open the properties of the database. Go to the Filegroup tab and add FileSTREAM:

Step3: Go to the Files tab and add a File in the file category as 'FILESTREAM Data':

Creation of the File Group:

Create the File Group by executing the following query:

CREATE TABLE FileDropLoc AS FileTable

WITH

(FileTable_Directory = 'FileDropLoc');

Then you can access the file location as:

\\<ip-address>\mssqlserver\DarkHorse\FileDropLoc

Place a file in the above path and run the following query:

You can find one record is present inside the File table. 

You can also find the file table created under the File table hierarchy 

Now you can write a after insert trigger on the File table and can execute the package that you want by using dtexec command line utility or you can run the SQL server job which intern run the package.