SSIS: Dynamic file naming

SSIS: Dynamic file naming

Some times, Files might need to be renamed dynamically based on current date (and or time) during a typical ETL process.

File Connection manager could come very handy for this. First initialize your package scoped string typed variable as DirectoryPath pointing to lets say "c:\mydir\mypath". Then use this user defined variable for connectionstring property in the expression for file connection manager. Following formula could be used for expression

@[User::DirectoryPath] + "\\" + (DT_WSTR,4)YEAR(GETDATE())
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()), 2) + ".txt"

As you notice, Year(GetDate()) is typecasted as DT_WSTR. Refer to books online for further help on typecasting.

Simply point your data source such as ole db to the file task based on the above file connection manager and your resultant file would be created based on dynamic name.

HTH..

ZULFIQAR SYED

Comments

[Surendar]

Thanks for the information as it helped a lot. I find some problem while trying to use the same method for time.
Atlast find this code and it worked cool.

+(DT_WSTR,4)DATEPART("DAY",Getdate())

+(DT_WSTR,4)DATEPART("MONTH",Getdate())

+ RIGHT("0" +(DT_WSTR,4)DATEPART("YEAR",Getdate()),2)

+(DT_WSTR,2)DATEPART("HOUR",Getdate())

+(DT_WSTR,2)DATEPART("MINUTE",Getdate())

+(DT_WSTR,2)DATEPART("SECOND",Getdate())

[FP]

I think, you have to set "EvaluateAsExpression=True" for that particular variable in the property tab, to make it work. Please advise if otherwise.

And ZULFIQAR SYED, you rock man. A true guru of SSIS. :)

FP

[RJT]

ZULFIQAR SYED, FP - KUDOS. Just what I needed - thanks.