January 19, 2009 at 11:41 pm
Hello all!!!
I am new to SSIS and i need to test few packages.
When i tried executing a package,i got the following error:
Error: Script threw an exception . Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding......
Can anyone help me on this??
January 19, 2009 at 11:43 pm
What is the script doing? The devil is in the details.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 19, 2009 at 11:50 pm
Unloads the data from a table ....
January 20, 2009 at 12:05 am
Any chance we might see some code?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 20, 2009 at 2:10 am
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=HR_Recruitment_Operational_Reports_StagingDb")
AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
conn.Open()
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.[Abacus_PPSUnloadDimension]"
cmd.Parameters.AddWithValue("@DimensionName", DbType.String).Value = "IDC Recruitment Portal:CandidateInfo"
cmd.ExecuteNonQuery()
conn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)
Dim sqlEvent As System.Data.SqlClient.SqlError
For Each sqlEvent In args.Errors
Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False)
Next
End Sub
End Class
January 20, 2009 at 6:44 am
Okay, the first thing is you are not changing the query timeout in the connection string and believe by default that is 30 seconds. Of course most processed on SQL Server should complete in under 30 seconds, especially when you are not returning any data.
What is the code in the SP? This is what needs to be optimized.
What happens if you run the SP in SSMS?
Why are you using a Script Task instead of the Execute SQL Task?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 2:21 am
Thanku 4 reply Jack !!!
And I m sorry..i couldnt reply u back...
Where can i change tht default setting for timeout...
In the package,there are few script tasks and Execute Process tasks..
February 4, 2009 at 6:35 am
TO change the query timeout you can do this:
cmd.CommandTimeout = [desired timeout in seconds]
You still didn't answer my questions as to what happens when you run the sp in SSMS and if you looked at it to see if you could optimize the T-SQL Code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 5, 2009 at 2:11 am
Stored Proc is for deleting the data from a table.
i have used the cmd.commandTimeout property...
Even then,it is giving the same error..
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply