?
Solved

SSIS: Move file to archive folder and rename incrementally

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

764 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