Solved

SSIS expression syntax (w/ variables)

Posted on 2010-11-08
8
316 Views
Last Modified: 2012-05-10
I have a variable
@{User::Path)
I need to append yesterday's date to this variable in this format (yyyymmdd) to the path. For example,
20101107 (If a month or day is single digit, it needs to precede with a zero '0')
0
Comment
Question by:spirose
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34086171
this was solved earlier.

you can see this link for step by step resolution.
http://www.sqlnerd.com/ssis_dynamic_dates.htm
0
 

Author Comment

by:spirose
ID: 34086213
How do I modify this expression to get yesterday's date instead of today's? Thanks in advance!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34086592
This way

SELECT  CONVERT(VARCHAR(10),DateAdd(day,-1,GETDATE()),112)
      
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:spirose
ID: 34086610
Sorry but this has to be in SSIS expression syntax....
The above only works in SQL Server...
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 125 total points
ID: 34086677
You can use the below exp
@[User::Path]+(DT_STR,4,1252)YEAR(Dateadd("D",-1,getdate()))+
Right("0" + (DT_STR,4,1252) Month(Dateadd("d",-1,getdate())),2) +
Right("0" + (DT_STR,4,1252) Day(Dateadd("d",-1,getdate())),2)

Open in new window

0
 
LVL 16

Expert Comment

by:vdr1620
ID: 34086697
Actually you can decrease the size..use this modified Exp
@[User::Path]+(DT_STR,4,1252)Year(Dateadd("D",-1,getdate()))+
Right("0" + (DT_STR,2,1252) Month(Dateadd("d",-1,getdate())),2) +
Right("0" + (DT_STR,2,1252) Day(Dateadd("d",-1,getdate())),2)

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34086715

I should say sorry.
anyways try this.

Select REPLACE(CAST(DT_DBDATE,DATEADD("day", -1, getdate())),"-","")

there could be some syntax error as I dont have anything to test it
0
 

Author Closing Comment

by:spirose
ID: 34086754
Thank you!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question