Solved

SSIS: Move file to archive folder and rename incrementally

Posted on 2013-11-06
3
3,962 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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