Script task to move multiple files to Archive Folder

Question

Hi All

I am new to SSIS,I want to move a file from one folder to another folder with current date attached to it. I am also putting the code that I have tried till now in SCRIPT TASK and this code works fine for moving single file.

But currently my situation is to move MULTIPLE files in the folder to another folder,

How can i resolve this

Any help Would be great.

Thanks

Vinesh

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

 <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

     ' The execution engine calls this method when the task executes.

    ' To access the object model, use the Dts object. Connections, variables, events,

    ' and logging features are available as static members of the Dts class.

    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    ' To open Code and Text Editor Help, press F1.

    ' To open Object Browser, press Ctrl+Alt+J.

     Public Sub Main()

         Dim SourceFile As String

        Dim DestinationFile As String

         SourceFile = "\\ccs-ver\CCS\VINESH\SSIS\ORGDATA\Template1.csv"

        DestinationFile = "\\ccs-ver\CCS\VINESH\Archive\Template1_" + Date.Now.ToString("yyyyMMddhhmm") + ".csv"

         If File.Exists("\\ccs-ver\CCS\VINESH\SSIS\ORGDATA\Template1.csv") Then

            File.Move(SourceFile, DestinationFile)

         End If

         Dts.TaskResult = DTSExecResult.Success

    End Sub

 End Class

Answer 1

SSIS has a cool feature called a ForEach loop. You can use this to accomplish your task without code. Here is one of the articles desribing how to do it: http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html#!/2007/03/ssis-file-system-task-move-and-rename.html

---

Kathi Kellenberger

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz