Solved

SSIS: Move file to archive folder and rename incrementally

Posted on 2013-11-06
3
3,864 Views
Last Modified: 2016-02-11
Hi All,

I've created a basic File System task that moves a file after some other SSIS tasks have finished with it.

The issue is I want to move files with the same name on a daily basis to an archive folder but I get a failure since obviously I didn't select to overwrite the existing files in the File System task configuration. The source file name is a variable.

Is there a way I can just increment the file I'm moving by some number or use the current date so I can keep all files after they've been processed? Or is there an even alternative better way of doing this?

Thanks,

OS
0
Comment
Question by:onesegun
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 300 total points
Comment Utility
Most developers I know in this situation will move the file to an archive folder, and rename it with a timestamp suffix like _YYYYMMDD.

The benefits are:
It doesn't overwrite any files
Makes it easier for prod support to identity past files
In a VB.NET script task, with sSource Folder as a read-only variable, and sArchiveFile / sArchiveFolder as read-write variables, that would go like this..
    Public Sub Main()
        Dim sSourceFolder As String, sArchiveFolder As String, sArchiveFile As String

        sSourceFolder = Dts.Variables("sSourceFolder").Value

        'Add subfolders 'Archive' and the YYYYMMMDD of the run
        sArchiveFolder = sSourceFolder & "\archive\" & DateTime.Now().ToString("yyyyMMdd") & "\"
        sArchiveFile = sSourceFolder & "\archive\" & DateTime.Now().ToString("yyyyMMdd") & "\" & DateTime.Now().ToString("yyyyMMdd") & ".zip"

        If Directory.Exists(sArchiveFolder) = True Then
            'Folder Exists
        Else
            'Folder does not exist, so create it. 
            Directory.CreateDirectory(sArchiveFolder)
        End If

        Dts.Variables("sArchiveFolder").Value = sArchiveFolder
        Dts.Variables("sArchiveFile").Value = sArchiveFile

    End Sub

End Class

Open in new window

Then the next task moves the file to variable sArchiveFile
0
 

Author Closing Comment

by:onesegun
Comment Utility
Hi Jim,

Once again thanks for you help.

The only things I had issues with were:

1)I had to set the DelayValidation property on the File System Task otherwise I was getting a failure at the File System Task passing the destination variable to it.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ca6d5af-3aa5-499a-8893-ef1aeada6913/variable-is-used-as-a-source-or-destination-and-is-empty?forum=sqlintegrationservices
2)The zip folder didn't work so I just commented it out and set the destination to sArchiveFolder. I amended the date function slightly to put the date and time stamp on the folder.

All in all good.

Cheers
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>1)I had to set the DelayValidation property on the File System Task
Forgot about that. You have my permission to smack across the head anyone that tries to tell you that setting DelayValidation to False is a Microsoft standard, and therefore it should always be False.

Reason:  If you're creating a new file, what's the point of validating if it exists in the first place?  (Insert various 'Duhh....' follow-ons here).

>2)The zip folder didn't work
Didn't see a reference to zip in your original question.  I've done this in the past but with <warning:  shameless plug> Pragmatic Works' Task Factory suite of controls, Zip control.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now