Dynamically Set SQL Task 1

[OrWolf]

I'm hoping that someone can give me code sample to get me going on this. I have a connection to a UniVerse database in my DTS package and I'm unable to get it to use any variation on Date() to get the current date. My next idea is to set the SQL Task SQL Statement using an Active X script. I've found some code dealing here and there, but nothing that I've been able to get working. I just need to point to a SQL Task in the current DTS package and set the SQL statements so it includes today's date. All help and suggestions are GREATLY appreciated.

Thanks!

 

[AlexCuse]

I modified some code I stole a long time ago to write this. I think it was from SQLDTS.com. What it was doing is building an entire query, then passing it to the source of a data pump task. With a few minor changes it works for an Execute SQL task.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
	Dim oPkg, oSQLTask, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "SELECT * FROM dbo.TABLE WHERE DOB_MMYY = '" & _
		DTSGlobalVariables("@D1").Value & "'"

	' Get reference to the ExecuteSQL Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oSQLtask = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

	' Assign SQL Statement to Source of ExecuteSQL task
	oSQLtask.SQLStatement = sSQLStatement

	' Clean Up
	Set oSQLtask = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

If I was wrong in reading your post, and you actually need this for a data pump task, you might want to change some variable names, but the key will be changing the reference to the task name to this:

DTSTask_DTSDataPumpTask_1

HOpe this helps you out,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[OrWolf]

This worked perfectly. Thank you!

 

[AlexCuse]

No problem. And by the way, that SalesLogix crm system that our client switched to is turning into even more of a nightmare than expected. Half the days they can't even deliver the files. Thanks for the warning though!

A wise man once said
"The only thing normal about database guys is their tables".