none
Timeout error when executing SQL command from the Script Task

    Question

  •  

    The SQL query takes about 4 minutes to execute in SQL Management Studio.  But when I run it from the Script Task using the OleDBCommand, I got the timeout error in 40 seconds.  The timeout setting on the SSIS DB connection is 0.  I do not see how to set the timeout for an SSIS task.  Any thoughts on this problem?  TIA. 

     

     

    Friday, April 18, 2008 12:25 AM

Answers

  • I found the solution. 

     

    With either SQL provider, .Net or OleDb for SQL server, the command timeout property has to be set for the sql command in the script as well.  One posting I saw claims that the timeout set for the DS for an SSIS package is not inherited and has to be set again for the sql command. 

     

    A side note on the performance comparison b/t the different SQL server providers.  OleDb for SQL server is about 30 seconds faster than the .Net provider for SQL server in my case.  To process over 7 millions records, OleDb: 3 min 41 sec vs. .Net: 4 min 10 sec. 

    Friday, April 18, 2008 4:53 PM

All replies

  • Try to use another provider. Management Studio uses SQL native provider. So, try to use

    System.Data.SqlClient.SqlCommand instead of System.Data.OleDb.OleDbCommand. If it won't help - we may continue thinking about this problem :-)

     

    Friday, April 18, 2008 1:03 AM
  • I'm still getting the timeout error. 

     

    By using System.Data.SqlClient in the Script Task, I have to change the DS provide in SSIS from Native OLE DB\SQL Native Client to .Net Providers\SqlClient Data Provider.  That is why I was using OleDbCommand before.  In my Script Task, I'm using the DS definied in the package. 

     

    So it is still not working.  Thanks anyway.

    Friday, April 18, 2008 4:29 PM
  •  

    One additional information.  I tried to process a small number of data in the script, it was actually finished in SSIS.  That makes me think the solution is to change the timeout for the Script Task somehow.
    Friday, April 18, 2008 4:31 PM
  • I found the solution. 

     

    With either SQL provider, .Net or OleDb for SQL server, the command timeout property has to be set for the sql command in the script as well.  One posting I saw claims that the timeout set for the DS for an SSIS package is not inherited and has to be set again for the sql command. 

     

    A side note on the performance comparison b/t the different SQL server providers.  OleDb for SQL server is about 30 seconds faster than the .Net provider for SQL server in my case.  To process over 7 millions records, OleDb: 3 min 41 sec vs. .Net: 4 min 10 sec. 

    Friday, April 18, 2008 4:53 PM
  • Hello,

       I am using ODBC connection to connect to SQL in a script task. While execution of the package the following error is thrown :-

     

    "The script threw an exception: ERROR [HYT00] [Microsoft][SQL Native Client]Query timeout expired"

     

    But this is very random. The error is coming randomly. Sometime the package runs and sometimes not.

     

     

    Thanks,

    Friday, August 01, 2008 11:42 AM
  •  

    Hi,

     

    Can you provide me with a code snippet which can be used to establish a ADO.Net ODBC connection with command timeout settings.

     

    Mohit

    Saturday, August 16, 2008 6:40 AM
  • I am experiencing the same issue.  I was forced to create a custom script because the SQL script that I needed to build dynamically was greater than the 4000 character expression limit.

     

    Are there any settings at the VB script level I should consider using to make the SQL query run faster on the server?

     

    Here's the relevant code:

     

    Dim myADONETConnection As SqlClient.SqlConnection
               myADONETConnection = _
                DirectCast(Dts.Connections("EDWPreStageADO").AcquireConnection(Dts.Transaction), _
                SqlClient.SqlConnection)

            Dim sqlCmd As SqlCommand = New SqlCommand()
            sqlCmd.Connection = myADONETConnection
            sqlCmd.CommandTimeout = 14400
            sqlCmd.CommandType = CommandType.Text

            Dts.TaskResult = Dts.Results.Success

            If Not String.IsNullOrEmpty(strNonPrimaryKeyWhereClause) Then
                Try
                    sqlCmd.CommandText = GetUpdateOldRecordsSQL()
                    Dts.Events.FireInformation(1, String.Empty, sqlCmd.CommandText, String.Empty, 0, True)
                    sqlCmd.ExecuteNonQuery()
                Catch ex As Exception
                    Dts.Events.FireError(1, String.Empty, strTableName + " " + ex.ToString, String.Empty, 1)
                    Dts.TaskResult = Dts.Results.Failure
                End Try
            End If

    Friday, August 22, 2008 7:30 PM