Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS: Move file to archive folder and rename incrementally

Posted on 2013-11-06
3
Medium Priority
?
4,152 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 66

Accepted Solution

by:
Jim Horn earned 1200 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 66

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

597 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