• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

SSIS 2008 -- in the SEND MAIL TASK >> Expressions>> MessageSource, how do I specify current date?

Hi,

I'm using SSIS 2008 and SQL SERVER 2008R2.

I want to get the current datestamp instead of  @[System::ContainerStartTime]   but I don't know what it's called.

In SSIS, I have a SEND MAIL TASK, then in  EXPRESSIONS, I have set MessageSource to :

        (DT_STR, 100, 1252)  @[System::ContainerStartTime] + " -- " + "Package completed -- SUCCESS"
0
paultran00
Asked:
paultran00
  • 6
  • 2
3 Solutions
 
brad2575Commented:
You can do this to get date parts and build out your own date format

 (DT_WSTR,4)YEAR(GETDATE()) +  RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2)
0
 
paultran00Author Commented:
Hi. Thanks for replying.   I also want the time HH:MM:SS and AM or PM  so what would the code look like?
0
 
brad2575Commented:
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) +
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
paultran00Author Commented:
How do I get the GETDATE() in this format:  03/23/2018 11:36:19 AM
0
 
paultran00Author Commented:
I figured it out with your help:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2)  + "/" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) + "/" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() )  + " " +(DT_STR,2,1252)DATEPART( "hh" , getdate() ) + ":" +  (DT_STR,2,1252)DATEPART( "mi" , getdate() )  + ":" +  (DT_STR,2,1252)DATEPART( "ss" , getdate() )
 + " -- " + "Package completed -- SUCCESS"

RESULT:

03/23/2018 11:55:48 -- Package completed -- SUCCESS
1
 
paultran00Author Commented:
Hi, do you know why the 7 minutes is missing the leading 0?  How do I fix it so that it has the leading 0?

RESULT:

03/23/2018 13:7:46 -- Package Completed
0
 
paultran00Author Commented:
I got it:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2)  + "/" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) + "/" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() )  + " " +  RIGHT("0" + (DT_STR,2,1252)DATEPART( "hh" , getdate() ), 2) + ":" +  RIGHT("0" + (DT_STR,2,1252)DATEPART( "mi" , getdate() ), 2)  + ":" +  RIGHT("0" + (DT_STR,2,1252)DATEPART( "ss" , getdate() ), 2)

RESULT:

03/23/2018 13:17:46
0
 
paultran00Author Commented:
Returns the current datestamp in the format that I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now