onsdag 13 maj 2009

Move a file and add a timestamp in SSIS

When handling different types of files in an SSIS package it is quite common that you move the file after it has been handled. For me it's also common that I want to add a filestamp to the file so that I know when it was handled.

This is the way that I do it which works fine for me...the method is built upon using variables (a powerful way to work in ssis). All variables are on package level....

filefolder = path to the folder where files to be handled are stored

filename = the name of the file to be handled, most likely you use the foreach loop to recieve the filenames from the path - dont forget to use name + extension to be retrieved...for example file.xls

thefile = is used to create the fully qualified file name including path. This variable is what you will use in your connection manager to be able to connect to the file. When you have created this variable make sure you mark it as an expression (EvaluateAsExpression=true). Then give it the following expression:

@[User::filefolder] + @[User::filename]

archivefolder = folder path to where you want to store the files eg: c:\files\archive\ Dont forget the last \

archivefile = variable used to create the new file name including timestamp. When you have created this variable make sure you mark it as an expression (EvaluateAsExpression=true). Then give it the following expression:

@[User::archievefolder] + SUBSTRING( @[User::filename] , 1 , FINDSTRING( @[User::filename],".",1) - 1 ) + "-" + SUBSTRING((DT_WSTR, 30)GETDATE(), 1,11) + SUBSTRING((DT_WSTR, 30)GETDATE(), 12,2) + SUBSTRING((DT_WSTR, 30)GETDATE(), 15,2) + SUBSTRING((DT_WSTR, 30)GETDATE(), 18,2) + SUBSTRING( @[User::filename] , FINDSTRING( @[User::filename],".",1) , LEN( @[User::filename] ) )

this will give the variable the following value: c:\files\archive\file-2009-01-19 142121.xls if the package started at 14:21:21 on the 19th of January 2009.

So what is the next step...
The next step is to use the File system task.

The operation you should use is: rename file (not move, rename!!!)
Destination variable is archivefile and source variable is thefile

Inga kommentarer:

Skicka en kommentar