1) Add a For Loop container and in the eval expression and 1==1 (so always returns true and will continuously loop).

2) Inside the container add a script task to check if the file exists. The below code will check if the file exists. If it does it will move onto the next step. If not it will pause for 1 minute and check again. Change "System.Threading.Thread.Sleep(numberofmiliseconds)" property to adjust the interval.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

Dim Answer As String

Answer = False

While Answer = False

If System.IO.File.Exists("yourfilelocation\yourfilename.xls") Then

Answer = True

Exit While

Else

System.Threading.Thread.Sleep(60000)

End If

End While

System.Threading.Thread.Sleep(60000)

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

3) Add the rest of the SSIS package transformations inside the container

4) Create an excel file with an auto_open macro in it along the lines of

Sub Auto_Open()

Application.DisplayAlerts = False

ChDir "YourFileLocation\"

Workbooks.Open Filename:="yourfilelocation\yourfilename.xls"

'Enter your macro data here to change the tab name and any other updates to the file

'Saves the file to the import location specified in the script task

ChDir "C:\_data\ToImport"

ActiveWorkbook.SaveAs Filename:="yourfilelocation\ToImport\yourfilename.xls", _

FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

'Closes the file and quits excel

ActiveWorkbook.Close

Application.Quit

Application.DisplayAlerts = True

End Sub

5) Open Task Scheduler on a machine that has office installed and schedule a new task to run. Under actions choose "Start a program" and add the location and name of the excel file just created in excel with the auto_open macro in it.

6) Start you SSIS package and it will continue to run until manually stopped. If a file is found in the ToImport folder the SSIS package will run through to the end and then loop back to the start again, otherwise it will keep repeating the script task until a file it found

Hope that helps.