Solved

SSIS expression syntax (w/ variables)

Posted on 2010-11-08
8
313 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
 

Author Comment

by:spirose
ID: 34086610
Sorry but this has to be in SSIS expression syntax....
The above only works in SQL Server...
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 52
testing sql16 on win10 vs OS16 2 36
grouping logic 6 50
Help Parsing a String with SQL Syntax 23 33
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now