Solved

SSIS: Move file to archive folder and rename incrementally

Posted on 2013-11-06
3
3,890 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
ID: 39627168
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
ID: 39627737
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
ID: 39627788
>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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

19 Experts available now in Live!

Get 1:1 Help Now