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


Upload Image Close it
Select File

I have around 6+ years of experience in SQL Server development and in Business Intelligence area. I am working as Datawarehouse developer. My blog space includes all technical areas related to SQL Server and MSBI (SSIS, SSRS and SSAS).
Browse by Tags · View All
SSIS 7
xml 3
dynamic 3
Union All 3
SSRS 2
Data flow task 2
Buffer 2
Time 2
Date 2
varchar(max) 1

Archive · View All
February 2013 3
September 2009 3
June 2013 2
January 2013 2
November 2012 2
July 2012 2
July 2009 2
April 2013 1
December 2012 1
February 2010 1

SSIS Expression for appending Datetime to the file

Jan 31 2013 12:00AM by Divya Agrawal Shah   

On of the many requirements in SSIS projects, is to append a Date and Time part to the file name which has to be uploaded. I have faced similar requirement many a times, and almost every time I struggle to create an expression to convert the Date and time value to the integer format and append to the file. This blog will prove really handy in such requirements.

I have a variable named “CurrentDate” which will take the Current System Date and store in it. We can achieve this using an Execute SqL Task with Single Row Result Set and map the Output paramert as Current Date.

image

image

 

We will use this variable CurrentDate in our expression to generate the Date and Time part.

Define a new User Variable “ExecutionDateStr”. Select the properties of the variable, Set Evaluate as Expression as True. Select the Expression  and open the Expression builder, paste the below expression:


(DT_STR,4,1252) DatePart("yyyy", @[User::CurrentDate]) +"" +
Right("0" + (DT_STR,4,1252) DatePart("m",@[User::CurrentDate]),2) +""+Right("0" +(DT_STR,4,1252) DatePart("d",@[User::CurrentDate]),2)+"_"+


Right("0" + (DT_STR,4,1252) DatePart("hh",@[User::CurrentDate]),2) +"" +
Right("0" + (DT_STR,4,1252) DatePart("n",@[User::CurrentDate]),2)  +"" +
Right("0" + (DT_STR,4,1252) DatePart("s",@[User::CurrentDate]),2)

 

 

image

Evaluate Expression, and you will find the evaluated value as 20130131_181024.

The part before underscore is Date and after that is Time part. All this converted into 112 date format for the ease of understanding.

Now, append this variable everywhere in your filename and your filename is ready to go.

Tags: SSIS, Expression, Date, time, filename, append


Divya Agrawal Shah
89 · 2% · 629
2
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

7  Comments  

  • Hi Divya,

    Really it will use full while handling the Staging Situations.The Data Type of the First Variable CurrentDate Is DateTime and ExecutionDateStr Data Type is String i hope

    Thanks.

    commented on Feb 1 2013 5:50AM
    Bala Krishna
    84 · 2% · 676
  • Correct, the data types are DateTime and string for CurrentDate and ExecutionDateStr. Thanks for pointing.

    commented on Feb 1 2013 6:57AM
    Divya Agrawal Shah
    89 · 2% · 629
  • One thing to add in this, I am taking the CurrentDate variable, as i have to create many files having the same date. This is to keep the date part unique in all files. If in your project, you have a single file, you can ignore CurrentDate variable and use "Getdate()" instead of it in ExecutionDateStr variable.

    commented on Feb 1 2013 6:59AM
    Divya Agrawal Shah
    89 · 2% · 629
  • Thanks For Your reply,

    commented on Feb 1 2013 10:41AM
    Bala Krishna
    84 · 2% · 676
  • Hi Divya,

    Why don't you build needed Date and Time string in T-SQL when you Execute SqL Task ?

    Thanks. Leonid

    commented on Feb 4 2013 12:27PM
    Leonid Koyfman
    50 · 4% · 1183
  • Hi Leonid,

    Your right we can get the result through T-SQL as well. See the following code

    SELECT CONVERT(VARCHAR(8),GETDATE(),112)+'_'+CONVERT(VARCHAR(8),GETDATE(),108)

    Then we can store it in a variable

    Thanks.

    commented on Feb 5 2013 1:16AM
    Bala Krishna
    84 · 2% · 676
  • Yes, we can definitely use Execute SQL task, however many a times we face situation like, we have the Date Range like FromDate and ToDate user Variables and we need to convert those variables into string format and pass to the SQL Command as variable. I have faced a similar situation. In this case we will normally avoid a round trip to SQL Server and get the calculation done, rather we can use an SSIS expression.

    commented on Feb 5 2013 2:33AM
    Divya Agrawal Shah
    89 · 2% · 629

Your Comment


Sign Up or Login to post a comment.

"SSIS Expression for appending Datetime to the file" rated 5 out of 5 by 2 readers
SSIS Expression for appending Datetime to the file , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]