SQL Server Performance

Dynamic SQL in DTS query

Discussion in 'SQL Server DTS-Related Questions' started by Will192, Apr 25, 2005.

  1. Will192 New Member

    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
  2. ChrisFretwell New Member

    Have you considered putting this into an sproc? Then having the DTS call the sproc?
  3. Will192 New Member

    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.

    What good would it do to put it in a stored procedure on my machine? The sql statement needs to be ran on the other box.

    Live to Throw
    Throw to Live
  4. Will192 New Member

    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.

    Thanks in advance for any responses to this post.

    Live to Throw
    Throw to Live
  5. Will192 New Member

    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
  6. Will192 New Member

    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
  7. Will192 New Member

    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
  8. Will192 New Member

    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
  9. Will192 New Member

    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

Share This Page