Dynamic SQL in DTS query - SQL Server Forums

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)

Dynamic SQL in DTS query

Author

Topic

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-04-25 : 14:25:29

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

Live to Throw
Throw to Live

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-25 : 14:41:58

You really should be doing this on the other box as one of it's own precoesses.

What's the database?

Brett

8-)

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-04-25 : 14:49:32

Like I said, I think that the database that I am pulling from is unix running ingres. I am not sure, and I can't get anyone that manages the system to tell me. I'm just happy that they gave me ODBC access.

I have to setup a pull to my box, I cannot setup a push on their machine. My machine is running SQL 2000. I would like to set this up in DTS on my machine.

Live to Throw
Throw to Live

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-25 : 15:25:27

How much data are you talking about?

If it's not a ton, maybe you can DTS the entire table to a staging table, then do the what you need on your box.

Brett

8-)

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-04-25 : 15:33:58

I have tried to pull down the entire table, but it times out.

I have tried to pull down different ranges of dates, but the only one that doesn't time out is if I pull a single day.

I know that it is indexed on the date column, and that's the only column that I need to query on. I am pretty sure that the database engine is ingres.

If I can just find a way to pull the current day's schedules by changing the text inside the '' of the sql statement, then I am off and running.

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-11 : 10:00:18

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.

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-11 : 11:13:46

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.

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-11 : 12:10:27

I changed line 27 to reference SQLStatement instead of SourceSQLStatement and it worked! Thanks for any replies to this post.

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-11 : 12:13:20

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?

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-12 : 10:26:58

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.

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2005-05-12 : 12:38:18

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.

Live to Throw
Throw to Live

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources