DTS Dynamic SQL 1

[Will192]

What I need to do is setup a DTS pull from another system. The problem is that the machine that I am pulling from is VERY VERY slow. The only query that I can run without timing out the ODBC connection is :

SELECT * FROM SCHEDULE WHERE DATE='2005-04-25'

What I need to do is setup a DTS job that pulls the schedules for just the current day. The machine that I am pulling from is not a SQL Server machine. I am pretty sure that it's a VERY old unix box running ingres. If I try a BETWEEN statement, it times out. If I try to put in a calculated function in place of the '2005-04-26', it times out.

I need a way to change the sql in the Transform Data Task of the DTS job. Can I just modify the system table and change the sql there? If so, then what table would I change?

I know that everyone is going to tell me to just define some variables and put the ? in the query. I have tried this and I get an ODBC error. Maybe I have the syntax wrong. I setup the variable in the GUI and made the following change to the SQL:

SELECT * FROM SCHEDULE WHERE DATE=?

It parses, but then I get the following error when I run it:

Multi-step ODBC DB operation generated errors. Check each OLE DB status value, if available. No work was done.



Thanks in advance for any replies to this post.

Will

 

[ProdAir]

and you set up the input parameter for the date?

 

[Will192]

I setup a global variable as CURDATE and defined the value. I then selected CURDATE as the value for parameter 1. When I run the statement with these changes and the ? in the statement like above I get the above error.

I assume that the ODBC driver can't handle the substitution, but I am hoping that it is something that I am doing wrong in the setup on my end.

 

[Will192]

Ok, here's my code of my ActiveX object. It's the first thing executed in the package.



Function Main()
Dim SelectStmt

SelectStmt = "SELECT * FROM schedule WHERE status='A' and DATE= '" & CStr(Year(Date)) & "-"
DeleteStmt = "delete FROM IEX..IEXSCHEDULE WHERE CONVERT(CHAR(10),DATE,21)= '" & CStr(Year(Date)) & "-"

if Month(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Month(Date)) & "-"
DeleteStmt = DeleteStmt & "0" & CStr(Month(Date)) & "-"
else
SelectStmt = SelectStmt & CStr(Month(Date)) & "-"
DeleteStmt = DeleteStmt & CStr(Month(Date)) & "-"
End If

if Day(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Day(Date)) & "'"
DeleteStmt = DeleteStmt & "0" & CStr(Day(Date)) & "'"
else
SelectStmt = SelectStmt & CStr(Day(Date)) & "'"
DeleteStmt = DeleteStmt & CStr(Day(Date)) & "'"
End If

DTSGlobalVariables("SQLString").Value = SelectStmt
DTSGlobalVariables("DelString").Value = DeleteStmt

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSDataPumpTask_1").CustomTask
oDataPump.SourceSQLStatement = SelectStmt

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_1").CustomTask
oDataPump.SourceSQLStatement = DeleteStmt


Main = DTSTaskExecResult_Success
End Function




I have verified that the following are the names of my delete and insert portions of the package.

DTSStep_DTSDataPumpTask_1

DTSStep_DTSExecuteSQLTask_1



When I run my package, I get the following error:

Task 'DTSStep_DTSDataPumpTask_1' was not found.



I pull up the workflow properties of each object and it's exactly the same. Not sure what I am doing wrong. Thanks for the quick reply.

Keep in mind that I am running SQL Server 7 on this machine. I get this working in SQL Server 2000, but not in 7. DTS isn't as robust in 7 as it is in 2000. There is no Dynamic Properties Task in 7.

Thanks in advance for any responses to this post.

 

[Will192]

Ok, I changed DTSStep to DTSTask and ran it. Now I get the following error:

Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description - object doesn't support this property or method: 'oDataPump.SourceSQLStatement'

Error on Line 27


Is there anywhere that shows all of the methods and properties for all of the tasks and connections in the DTS manager? I need to change the SQL for my Execute SQL step and for my Data Transformation step.

The Execute SQL step is a yellow cylinder icon with red circular arrows in front of it. The Data Transformation step is just a arrow pointing from the source to the destination server.

 

[Will192]

I got the error fixed by changing line 27 to reference SQL Statement instead of SourceSQLStatement.


Ok, here's the latest. I changed my ActiveX code to the following:

Function Main()
Dim DeleteStmt, SelectStmt, oPkg, oDataPump, sSQLStatement, PullDate

PullDate = "2005-05-05"
SelectStmt = "SELECT * FROM schedule WHERE status='A' and DATE= '"+PullDate+"' "
DeleteStmt = "delete FROM IEXSTAGE35..IEXSCHEDULE WHERE CONVERT(CHAR(10),DATE,21)= '"+PullDate+"' "

DTSGlobalVariables("SQLString").Value = SelectStmt
DTSGlobalVariables("DelString").Value = DeleteStmt

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
oDataPump.SQLStatement = DeleteStmt

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oDataPump.SourceSQLStatement = SelectStmt

Main = DTSTaskExecResult_Success
End Function


This works fine as written. What I want to be able to do is to dynamically change the value of the PullDate variable so that I can run this for whatever day I want, or maybe pull the days to run from another table. Any ideas?

 

[Will192]

Here is what my question boils down to:

How do I store the results of a query (one value, not a set) to a global variable?

I can do this in 2000 easily, but I can't find any documentation on it in 7. Upgrading the server is not an option at this point in time.

 

[Will192]

I replaced the following line :

PullDate = "2005-05-05"

with the following code :

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={SQL Server};Server=(local);Trusted_Connection=yes;"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "SELECT COL_DAT FROM PULLDATE", oConn
PullDate = oRS(0)
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing

And it works! Now all I have to do it change the value of the row that I keep in the PULLDATE table and run the DTS job. I am going to scroll through a list of dates and just change the value in the table for each time that I run the job

Thanks to all that have posted on this thread.