Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create variable for @startdate and @endDate in SSIS Execute SQL Task Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 48, Visits: 218
I have a simple query that provide me with all the previous month data using the FilecreationDate

Declare @StartDate varchar(8)
Declare @EndDate varchar(8)

Set @StartDate = Replace(convert (varchar(8), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),112),'/','')

Set @EndDate = Replace(convert(varchar(8), DATEADD(dd, -DAY(DATEADD(m,1,GETDATE())), DATEADD(m,0,GETDATE())),112),'/','')

Select * FROM mytable
where FilecreationDate between @StartDate and @EndDate

I want to use the above query to export the previous month data using the Execute sql task

Variables:
StartDate value Replace(convert (varchar(8), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),112),'/','')

EndDate value Replace(convert(varchar(8), DATEADD(dd, -DAY(DATEADD(m,1,GETDATE())), DATEADD(m,0,GETDATE())),112),'/','')

My query in Execute SQL Task
Select * FROM [dbo].[myTable]
where FileCreationDate between ? and ?

when I build my ssis and run I get every record from the table. what am I doing wrong. It looks like my variable parameter is not working. All I want are only records from the 1st of last month to the end of last month.
Post #1517409
Posted Monday, November 25, 2013 12:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 48, Visits: 218
I have solve my issue. I did not need Execute SQL Task. I used sqlcommand
Post #1517429
Posted Wednesday, December 11, 2013 10:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
Just an FYI if you are using the CONVERT with 112 you don't need the REPLACE statement the 112 does not return the results with the slashes(yyyymmdd).
Post #1521978
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse