File renaming - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

File renaming

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-27 : 09:09:29

I have had a go at using a package with a script to rename and move a file and it works well by using a script task in a package with source and destination variables. See script at bottom
But in my scenario the file thst comes in every day will have a slightly different name. It will be called "System_UT_INCR_BOOKINGHEADER_20080228000000.TXT"
On the 28th Feb. The date part of the title changes everyday.

SO i need to adjust my "Source" variable which is currently just a string with a value of "C:\Data\files\imports\System_UT_INCR_BOOKINGHEADER_20080228000000.TXT" So it will only ever look for that exact file name

Imports System.IO
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.

	Public Sub Main()
        Try
            File.Move(Dts.Variables("Source").Value.ToString, Dts.Variables("Destination").Value.ToString)
            Dts.Events.FireInformation(0, "", "File Moved Succesfully", "", 0, True)
        Catch ex As Exception
            Dts.Events.FireError(1, "", "Source file or destinations does not exist", "", 0)
        End Try

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-02-27 : 10:18:27

You could set your variable to evaluate as an expression, and have that create the string based on the current date. However, SSIS expressions can be a bit unwieldy for this sort of thing, so it's probably easier to declare a new string variable in your script and initialise it using date and string manipulation functions.
As an aside, you should have a look at the File System Task, as you could probably accomplish most of what you need without a script task (you'd still have to dynamically set the connections though).
It's also worth considering whether you'd ever be looking for a file relating to day other than today.

Mark

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-27 : 10:36:16

Thanks Mark
i was just looking at file tasks and it seems a little easier to manage!
However, I think I want to ignore the date stuff and just look for a file that contains the string "BOOKINGHEADER" - can i do that somehow in my file system task?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-02-27 : 13:19:46

In that case, I'd use Foreach Loop Container with a Foreach File Enumerator. This can return any files in a given directory with names matching a specified pattern. Clearly this has the benefit of being able to cope with multiple files.
I'd suggest the following:
1. Create and initialise the following variables:
- SourcePath
- DestinationPath
2. Additionally create the following variables:
- SourceFilename
- DestinationFilename
In the properties of each, choose "EvaluateAsExpression" = True, and set the expression to "@[User::SourcePath] + @[User::SourceFileName]"
and "@[User::DestinationPath] + @[User::SourceFileName]" respectively.
3. In the 'Collection' section of the Foreach Loop Editor, expand Expressions and set 'Directory' to @[User::SourcePath].
4. Enter the pattern of the filename in the "Files:" text box (i.e. "*BOOKINGHEADER*.txt") (Note. this could also be read from a variable)
5. Choose "name and extension" in the "Retrieve file name" section
6. In "Variable Mappings" specify the SourceFilename variable
7. Drop a File System Task into the Foreach Loop Container.
8. Set IsDestinationPathVariable = True, specifying the DestinationFilename variable, and do the same for the SourceFilename variable.
9. Choose the appropriate operation (i.e. copy file).

You should be good to go!

Mark

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-28 : 01:58:53

Thanks so much really appreciate that!

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-29 : 00:28:39

I've set everything up and double, triple checked it but everytime i run it i get a message saying
Warning The foreach file enumerator is empty. Can't find files that match the pattern or the directory is empty.
The file is there and the directory paths are correct

steve14437
Starting Member

4 Posts

Posted - 2008-02-29 : 16:49:40

Under SQL 2000 DTS, I used to use a different approach which worked well for this type of thing.

You already have a loop process set up which checks the source directory for your files. (I see you're having trouble with that currently, and unfortunately, I don't have a solution for that part of the problem.) Once you find the correct file, copy the file to a new filename ("load.txt") and use this filename in your package as the file to load each iteration. When the load is completed, drop the copy and move your original to storage. This way you can avoid having to use an expression as your source filename.

HTH,
Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources