Post reply

Problem Executing 1 DTS Package Many Times With Different Connections

  • November 18, 2004 at 8:31 am

    #60655

    Please forgive the long subject.  It is hard to desribe what I am doing.

    I am using SQL Server 2000 Enterprise Edition and clustering on computers with multiple CPU's.

    I have one DTS package on a server I'll refer to as DTS Server.  I have 6 destination servers with identical databases.  I have 1 data pump DTS package that will pump data from the DTS Server to one of the designated destination servers.

    I have a main DTS package with one ActiveX Script Task per destination server.  Each ActiveX Script Task sends in the server name via a global variable to the data pump DTS package.  The data pump DTS package uses a Dynamic Properties Task to change the connections before the data pumps start.  All six start at about the same second.

    The problem is I am getting strange errors and unexpected errors and am not figuring out what the problem is.  In a moment I will include some of the errors.  My best theory is that there is only one place to store the global variables and so when I execute the 1 DTS data pump package 6 times simultaneously some of the global variables are getting confused.

    Now for the errors:

    On an Execute SQL Task called Create Main Staging Tables I get the following error sometimes:

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

    This task runs immediately after the Dynamic Properties task that changes the connections which tell the package where to create the staging tables.  I don't always get this error.

    Here is an error this package logged:

    Step 'ExecuteSQLTask_Drop_oldTables' failed

    Step Error Source: Microsoft OLE DB Provider for SQL Server

    Step Error Description:Connection is busy with results for another command

    Step Error code: 80004005

    Step Error Help File:

    Step Error Help Context ID:0

    This task runs right after the task called Create Main Staging Tables.

    Here is one I don't understand:

    Step 'DynamicPropertiesTask_ChangeConnections' failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package

    Step Error DescriptionleDBProperty 'Identifier Case Sensitivity' already exists in the collection.

    Step Error code: 800403EC

    Step Error Help File:sqldts80.hlp

    Step Error Help Context ID:399

    This is the error I get when the Dynamic Properties Task tries to change the server name.  I don't get this one very often.

    Here's another one I don't understand:

    Step 'ExecuteSQLTask_RPT02' failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package

    Step Error DescriptionleDBProperty 'Asynchable Abort' already exists in the collection.

    Step Error code: 800403EC

    Step Error Help File:sqldts80.hlp

    Step Error Help Context ID:5300

    This is on a task that runs as soon as the package starts (there are six of these).  The task itself simply has a remark and no code to execute.  In the workflow section there is a VBScript that checks the server name global variable to determine which of the six gets executed and the other 5 don't execute.  Its sole purpose is so that I know which server the success/failure log entries are for.

    Forgive the long post.  If I don't get responses I will try breaking it up into smaller pieces.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • November 18, 2004 at 8:42 am

    #530683

    And of course, I was so focused on describing the problem I failed to ask any questions.

    Has anyone seen these errors before?  I have seen the "Multiple-step OLE DB operation generated errors" error before but I think I used to get that when using linked servers and in this Package I get the error simply for executing a task that has a remark in it and no code but uses the connection in question.

    Does anyone else execute 1 DTS package multiple times but modify the connections dynamically before each execution?

    Anyone know problems that can arrise because of executing 1 DTS package multiple times that could cause the errors I am seeing?

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • November 24, 2004 at 6:42 am

    #531377

    I believe I have discovered the problem since it is no longer happening.

    I was getting all those errors because the changes made by the dynamic properties task were not all 100% in place when the next task started and tried to use the connection that was in process of being changed.  I put a delay in place to make sure the connection is not used for about 3 or 4 seconds and now everything is fine.

     

    I have seen this kind of delay in applying changes before.  Like when a task executes and is finished and the next task checks the status of the execution of the first task.  It always would come back as failed even though the previous task had succeeded.  I needed at least a 2 second delay to ensure the execution status was finished being set.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • December 11, 2007 at 10:11 am

    #759060

    I have the same issue -very random though. This may be it, but if I may ask what is probably a silly question; how does one add such a delay?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply