To take a backup of massive MySQL database, it is suggested to use an application that compresses the backup like 7Z or gzip and use MySQLdump utility placed in bin folder.

Please feel free to download gzip at gzip-1.3.12-1-bin

Imagine a situation where you are processing all files in a folder and few files need not be processed in SSIS.

Here are the steps:

1. Configure for each loop container to iterate thru a folder and store each file name in a variable (say FileName)
2. Use execute SQL task and then connect to the data flow task. Connect and use expression & constraint and use expression as follows

[cc lang=”SQL”]

findstring(@FileName, “2012-10-14”, 1) == 0

[/cc]

3. Also use a file system task to move the processed file to Archive folder

4. Even better if you can use checkpoint to start the package from the point of failure

SQL Server 2012 has a nice feature that enables to poll any shared directory using FileTables.
Here are the steps :

Enable filestream as shown below

Restart SQL Server service from the configuration manager

Enable filestream access level using sp_configure in TSQL

Create a filegroup that contains filestream

Add a filetable file to the above filegroup

Enable non-transactional access to the sharedfolder

Finally put all the .txt, .csv files to be automatically discovered by SQL Server

Below are the SQL commands:

[cc lang=”sql”]

— enable filestream access
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

— create a filegroup
ALTER DATABASE TestDB ADD FILEGROUP TestFG CONTAINS FILESTREAM;

— add the filetable file to the above FG
ALTER DATABASE TestDB
ADD FILE
(
NAME= ‘TestFile’,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestFile’
)
TO FILEGROUP TestFG;
GO

— enable above directory for non-transactional access
ALTER DATABASE TestDB
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’TestFile’ )

— create a filetable that polls above directory
CREATE TABLE TestFileTable AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = ‘TestFile’,
FILETABLE_COLLATE_FILENAME = database_default
);
GO

[/cc]

Below is the directory that is polled by SQL Server. It has 4 files as shown.

The same files can be queried in SSMS using the query as :

[cc lang=”sql”]
SELECT convert(varchar(max), [file_stream]) as readcontent
,[name]
,[file_type]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [TestDB].[dbo].[TestFileTable]
[/cc]