Batch file to move files from one directory to another

SET src_folder=C:\Users\shane.ryan\Desktop\88.29.66.131
SET tar_folder=C:\Users\shane.ryan\Desktop\88.29.66.131\Archive

for /f %%a IN ('dir "%src_folder%" /b') do move %src_folder%\%%a %tar_folder%

pause

How to call an SSIS package from the command line

 Alternatively save the script below as a batch file

echo "Starting Windows Task Scheduler - Batch File"
dtexec.exe /DTS "\"\MSDB\Load_FBDFiles_v01_13072016\"" /SERVER "\".\"" /CHECKPOINTING OFF  /REPORTING V
echo "Completing Windows Task Scheduler - Batch File"

The batch file can be called from an automated windows task

Here’s how to get there:

•       Open the Control Panel.
•        Open the Administrative Tools window.
•        In Windows 7, look under System and Security(Windows Vista, under System and Maintenance)
•        Open the Task Scheduler icon.
•        If prompted, type the administrator’s password or click Continue.
•        The Task Scheduler window appears.
•        Create new Task & Schedule ‘Load FBD Files Monthly’ accordingly, point task towards the following script “Load_FDB_Batch_WinTScheduler.bat” Ensure the task is set to run with the highest privileges. Set the schedule to desired frequency. The task should also be set to “Run whether user is logged in or not” and “Do not store password” should be checked.

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