Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Exploring SSIS - Rename file by suffixing current date

Feb 8 2013 12:00AM by Madhivanan   

Previous posts on this series

Exploring SSIS - Understanding the basics
Exploring SSIS - Execute SQL task with simple parameter
Exploring SSIS - Export to csv file

I have started a series on Exploring SSIS where I will explain various features available. In this post, we will see the usage of File System Task of Control Flow which can be effectively used to handle file related tasks like renmaing, copying, moving, etc. In specific we will see how to rename the existing file to suffix current date

One of the common requirements in ETL is to export data on a daily basis with common name for a file and suffix current date as part of the file name so that every day we will have different file

This is post is direct follow up of the following post

Exploring SSIS - Export to csv file

Please refer the above post on how to create data flow task that connects to SQL Server source and export data to a csv file. Using exactly the same tasks, I am going to add a File system task from Control flow that will do the renaming. Assuming that you follow the above post and set up a data flow task, now in the Control Flow area, right click and in the pop up menu, choose variables. In the variables window, click the icon labelled as Add variable. Type name as filename, let the scope be Package, choose data type as String and leave it blank for the column value

Go to the properties window for Variables , set EvaluateAsExpression as True, click on Expression and in the Expression Builder window, specify the following under Expression

"C:\\sysobjects"+(DT_WSTR,4)year(getdate())+right("0"+(DT_WSTR,4)month(getdate()),2)+right("0"+(DT_WSTR,4)day(getdate()),2)+".csv"

Click on the button labelled Evaluate Expression and make sure that you see the correct value as C:\sysobjects20130129.csv (if the current date is Jan 29, 2013)

Now Drag File System task and name it Rename file with current date. Double click on this and in the File System Task editor, set IsDestinationPathVariable to True, select the variable user::filename for DestinationVariable, set OverwriteDestination to True, set Operation to Copy File, set IsSourcePathVariable to False and choose the Flat File connection Manager as SourceConnection

Now execute the Control flow and check that in the C drive you will see a file with a name sysobjectsYYYYMMDD, where YYYY is four digit current year, MM is two digits month and DD is two digits day. For example if todays date is 29th Jan, 2013, the file name will be sysobjects20130129.csv, etc.

In addition to the screen shots shown at Exploring SSIS - Export to csv file, here are additional screen shots

file1

 

file2

 

file3

 

file4

 

file5

 

file6

Tags: 


Madhivanan
3 · 40% · 12909
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Hi Madhivanan,

    If you Rename the file by suffixing current date then you can use Rename file option instead of Copy file option in File System Task.

    Even though if your running the package second time,first Data Flow Task will get execute .So source file is always ready and there is no chance to package get failure.

    Please let me know is there any specific reason to use Copy File option instead of Rename File.

    Thanks for sharing original post

    commented on Feb 11 2013 6:27AM
    Bala Krishna
    83 · 2% · 676
  • Bala krishna, yes you can make use of that option too.

    commented on Feb 11 2013 11:59PM
    Madhivanan
    3 · 40% · 12909

Your Comment


Sign Up or Login to post a comment.

"Exploring SSIS - Rename file by suffixing current date" rated 5 out of 5 by 3 readers
Exploring SSIS - Rename file by suffixing current date , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]