using a date as a parameter in DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

using a date as a parameter in DTS

Author

Topic

ustreddogdog
Starting Member

3 Posts

Posted - 2004-09-20 : 05:10:24

I'm a new user using DTS. I have built a DTS which copies records from another server to the SQL server using a query "SELECT * FROM sourceTable WHERE sourceDate BETWEEN '2004/09/01'AND '2004/09/18'".
In fact I want to copy the records from the first date of this month to the day before current day. How can I set the parameter so that I need not change the date every morning?
Furthermore, I want to set a variable which stores the table name of the source database and pass the name to a executable for some calculation before the transcation?
Thanks for you help.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-20 : 05:44:18

Create a couple of global variables (each for start date and end date). Double-click your transform data task to access its properties. Rewrite the query in your datapump task replacing the dates with question marks, i.e. "SELECT Field1, Field2, etc FROM sourceTable WHERE SourceDate BETWEEN ? AND ?".
Click 'Parameters' and set the assign your global variables as required.

Mark

ustreddogdog
Starting Member

3 Posts

Posted - 2004-09-20 : 05:47:26

then how can i change my global variable automatically?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-20 : 06:19:39

Use an activeX script task to set your global variables. Something like:

Function Main()
Dim datCurrentDate
Dim datYesterday
Dim datFirstOfMonth
Dim intYear
Dim intMonth
Dim strMonthName

'Get current date
datCurrentDate = Date

'Get yesterday's date
datYesterday = DateAdd( "d" , -1 , datCurrentDate )

'Get month part
intMonth = Month(datYesterday)

'Get month name
strMonthName = MonthName(intMonth)

'Get year part
intYear = Year(datYesterday)

'Get first of month
datFirstOfMonth = DateValue("1/" & strMonthName & "/" & CStr(intYear))

'Set global variables
DTSGlobalVariables("gv_FromDate").Value = datFirstOfMonth
DTSGlobalVariables("gv_ToDate").Value = datYesterday

'Return Success
Main = DTSTaskExecResult_Success
End Function

Mark

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-20 : 06:53:28

how about setting a pattern for your dts and scheduling it?

i have the same setup

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources