DTS pull with dynamic sql
-
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
Will Summers -
Sounds like a syntax error. Do you have any other method of querying the database? Maybe a tool like Query Analyzer for SQL Server?
The reason I ask this is that we deal with a Progress database that runs on a Unix box. This exact sort of problem was driving me nuts till I worked out that instead of,
SELECT * FROM SCHEDULE WHERE DATE=?
The Progress syntax is,
SELECT * FROM "PUB"."SCHEDULE" WHERE "DATE"=?
Amazing what a few extra characters can do.
If you don't have a tool, I can recommend these ones, http://gpoulose.home.att.net/
--------------------
Colt 45 - the original point and click interface
-
I tried to put the quotes around the column and table name and it didn't work. The statement didn't even parse.
Live to Throw
Throw to Live
Will Summers -
I have run into that one too. Many ODBC drivers don't support the ? parameter in SQL DTS and return the error you got if you try to use it.
What I do in cases like this is to dynamically build the SQL statement within an ActiveX Task and store the resulting string in a global variable.
Then I use the Dynamic Properties task to update the Data Pump Task's SQL statement using that global variable value.
It works great in those cases where the ODBC driver doesn't support the ? parameter functionality in DTS.
-
How about going a roundabout way to do this? For example if its always a report for the current day, create a procedure that runs on the slow machine ( I assume you have rights to do this). The procedure will get the data and put it to a file either on itself or maybe to your machine (so the connection itself isnt timing out). Then it writes to a table that its done. Your DTS would then just poll that table until it sees that its done and then it can grab the data from the file which hopefully will not cause a timeout (doing a BCP into your system).
Again, its a roundabout way to do it, but if you're having such a problem with a timeout then sometimes finding a way to get the data in a different mode is needed.
Cheers
TNS
noob SQL developer
-
provost - the global variable thing is what I am trying to do. How do I get the global variable pumped into the sql statement that I am going to send to the other system.
Tim Smith - I don't have any other rights to the other system except the read-only ODBC connection.
Does anyone know where the sql statement is stored for my DTS job? I could just modify the text in the table everyday and all would be fine. Unless the statement is stored compiled, which I doubt because the text is going to be sent to another system.
Live to Throw
Throw to Live
Will Summers -
provost - how do I dynamically build the SQL statement within an ActiveX Task?
Live to Throw
Throw to Live
Will Summers -
Actually, you may not need to use the ActiveX/Dynamic Properties. Try changing your SQL statement to:
SELECT * FROM SCHEDULE WHERE DATE=TODAY
I think that Ingres supports the TODAY Keyword.
-
The statement that I have found is the following:
SELECT * FROM SCHEDLE WHERE DATE=CURDATE()
Unfortunately this statement times out. You would think that the database engine on this machine would be able to handle a simple statement like this, but it can't. The machine is just too slow and there are too many rows in the table.
I do need to pull data off this machine so that we can run reports.
Live to Throw
Throw to Live
Will Summers -
You can edit the connection timeout property in Disconnected Edit. Right click in the white space in your package to get there.
I like the ActiveX approach and use it a lot. Upstream from your transformation you will need an ActiveX script. To make things super easy, flow next to the Dynamic Properties task, and then to your transformation.
First create a global string (right click in the white space to do this). Call it sSQL.
Then create an ActiveX with the following code:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim sSQL
Dim dDate
dDate = Date() 'or whatever you need here
'maybe you want to get this date from an upstream
'execute sql task as an output parameter passed to a global
'that can be referenced here
sSQL = ""
sSQL = sSQL & " SELECT MYFIELD1, MYFIELD2"
sSQL = sSQL & " FROM MYTABLE"
sSQL = sSQL & " WHERE MYDATE = '" & Cstr(dDate) & "'"
DTSGlobalVariables("sSQL").Value = sSQL
Main = DTSTaskExecResult_Success
End Function
'************************************************************************
Create a Dynamic Properties Task.
On success of the ActiveX, go to the Dynamic Properties task
Click add. Set the SourceSQLStatement of the DataPumpTask (your transformation) to the global variable sSQL. Yes you can do this right in the ActiveX too, but this method is just SO simple!
On success go to the transformation.
Thats it! Have fun!
[font="Courier New"]ZenDada[/font]
-
OK. I'll assume that you have already created the Transform Data (Data Pump) Task.
1. Create a Global variable called SQLString.
2. Drag the ActiveX Task object to the DTS Designer Desktop. Go into its properties.
3 Replace the existing script with:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim SelectStmt
SelectStmt = "SELECT * FROM SCHEDULE WHERE DATE= '" & CStr(Year(Date)) & "-"
if Month(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Month(Date)) & "-"
else
SelectStmt = SelectStmt & CStr(Month(Date)) & "-"
End If
if Day(Date) < 10 then
SelectStmt = SelectStmt & "0" & CStr(Day(Date)) & "'"
else
SelectStmt = SelectStmt & CStr(Day(Date)) & "'"
End If
DTSGlobalVariables("SQLString").Value = SelectStmt
Main = DTSTaskExecResult_Success
End Function
3. Drag the Dynamic Properties Task Object onto the DTS Designer Desktop. Open its properties. Click Add. Click on the plus sign next to Tasks on the left side and look for the DataPump task. Click on it. On the right side you will see the properties for it. Scroll down to the property "Source SQL Statement". Double click on it. In the window that pops up, click the the dropdown and change the value from INI file to Global Variable. In the Variable dropdown below it, select the SQLString Global variable. Click OK until you get back to the DTS Designer Desktop.
4. Setup the workflow to execute ActiveX,Dynamic Properties, and the Data Pump Task in that order.
Cheers!
-
How cow. I got it to work. Well, actually you told me how to get it to work. Now that I have this base working, I can run with this. Thanks for the help.
Live to Throw
Throw to Live
Will Summers -
Ok, now I have to set this up in SQL Server 7. I have it all done except for the Dynamic Properties Task step. There is no Dynamic Properties Task in the Task list.
How do I do the dynamic sql in sql server 7?
Live to Throw
Throw to Live
Will Summers -
You will have to do it in the ActiveX script as SQL 7.0 doen't have the Dynamic Properties Task.
-
How do I reference the sql of the DTS object that runs my SQL code? (Sorry for the delay, but I got pulled off on another project)
Live to Throw
Throw to Live
Will Summers
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply