SSIS Dynamic FTP task & Unzip & Load Process

 I've developed an automated SSIS solution to download zip files from an FTP Server every month. Each month the code gets the latest version of a specific file i.e. filename_YYYYMM_full.zip, downloads it and unzips it and finally loads them into the target database

Open SQL Data tools  (Visual Studio 2010 Shell)
Create an SSIS project
Its possible to set the FTP parameters using both package variables and local user variables as per the screenshot below

Its possible to set package and local user variables to use expressions to make them dynamic e.g. varRemotePath1 is defined as @[$Package::FTPFileName2] + (DT_STR,4,1252)YEAR( getdate() )  + Right( "0" + (DT_WSTR, 2 ) MONTH(Getdate()),2)+ "_FULL.ZIP"

I installed 7zip and its possible to call this dynamically to pass in the dynamic filename and destination path e.g. "x "+ @[User::VarZipFilePath]+  (DT_STR,4,1252)YEAR( getdate() )  + Right( "0" + (DT_WSTR, 2 ) MONTH(Getdate()),2)+ "_FULL.ZIP" +  " -o"+ @[User::VarUnzipFolderPath] +" -aoa"
(This will overwrite existing files if they exist)

If DBmail is configured its possible to send email notifications with a dynamic subject line & Content from an Execute T-SQL task e.g.

-- send email confirming Schema number
DECLARE @DBServerName VARCHAR(20),
                @DBSubject VARCHAR(200)

set @DBServerName  = @@SERVERNAME
set @DBSubject = @DBServerName  + ': Load Report - Completed - Confirm Edition below'

-- send email confirming Schema number
EXEC msdb..sp_send_dbmail
        @profile_name = 'DBATEAM',
        @recipients = 'john.doe@mail.co.uk',
        @subject = @DBSubject,
        @query = 'SELECT [sBulletinText] as Edition FROM [statsdb].[dbo].[fdb_Bulletin] where iLineNumber = 2',
        @query_result_header=0
 commented out email notification */

The actual load process as below calls another couple of scripts which I will upload in another post

USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('[MyDB')

EXEC(@kill);
GO

--Delete the Database
Drop Database MyDB
GO

-- Recreate the Database
Create Database MyDB
GO

-- enable XP_CmdShell
Exec sp_configure 'Show Advanced Options', 1
Reconfigure
Exec sp_configure 'XP_CmdShell', 1
Reconfigure
GO

--copy scripts to directory
EXECUTE xp_cmdshell 'copy C:\etl\scripts\ C:\temp\Uncompressed\'
GO

--load the schema into the MyDB database
USE [MyDB]
DECLARE @DBServerName VARCHAR(20),
                @DBName VARCHAR(20),
    @FilePathName VARCHAR(100),
    @sql VArchar(200)
set @DBServerName  = @@SERVERNAME
set @DBName = 'MyDB'
set @FilePathName = 'C:\temp\Uncompressed\Create_Schema_SQL_SERVER2008.sql'
set @sql = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName
EXEC xp_cmdshell  @sql
GO

--Import Files into the MyDB database
USE [MyDB]
DECLARE @DBServerName VARCHAR(20),
                @DBName VARCHAR(20),
    @FilePathName VARCHAR(100),
    @sql VArchar(200)
set @DBServerName  = @@SERVERNAME
set @DBName = 'MyDB'
set @FilePathName = 'C:\temp\Uncompressed\ImportMyFiles.sql'
set @sql = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName
EXEC xp_cmdshell  @sql
GO

-- delete downloaded file example
EXECUTE xp_cmdshell 'del /Q C:\temp\Uncompressed\*.*'
GO

-- delete downloaded file example
EXECUTE xp_cmdshell 'del /Q C:\etl\downloads\*.*'
GO

-- disable XP_CmdShell
Exec sp_configure 'Show Advanced Options', 1
Reconfigure
Exec sp_configure 'XP_CmdShell', 0
Reconfigure
GO