Error while running script task in SSIS

• Alhad Tamdu-965284

  Valued Member

  Points: 67

  More actions

  August 19, 2009 at 3:26 am

  #138306

  Hi,

  I am getting error message while running script task in SSIS package:

  "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

  below is code that is return inside the script task:

  ' step 1: write data to log file.

  Dim NewFileName As String

  Dim TLDateTime As String

  Dim TLDay As Integer

  Dim TLMonth As Integer

  Dim TLYear As Integer

  Dim TLHour As Integer

  Dim TLMinute As Integer

  Dim TLDate As String

  Dim TLTime As String

  Dim TLSecond As Integer

  TLDay = DateTime.Now.Day

  TLMonth = DateTime.Now.Month

  TLYear = DateTime.Now.Year

  TLHour = DateTime.Now.Hour

  TLMinute = DateTime.Now.Minute

  TLSecond = DateTime.Now.Second

  Dim MyDate As New DateTime(TLYear, TLMonth, TLDay, TLHour, TLMinute, TLSecond)

  Dim MyString As String = MyDate.ToString("MMMddyyyy_HHmmss")

  TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString

  TLTime = TLHour.ToString + TLMinute.ToString

  TLDateTime = TLDate + "_" + TLTime

  NewFileName = "ErrorCategory" & MyString & ".txt"

  Dim fileName As String = "D:\CapabilityToolMatrix\CategoryXLS\ErrorFile\" & NewFileName

  Const COMPONENT_NAME As String = "Export Employees"

  'Const CONNECTION_STRING As String = "Server=dtp2k3-Swalekar\SQLEXPRESS;database=CapabilityMatrix;uid=test;pwd=test;Max Pool Size = 1079;connection timeout=1000000"

  Const CONNECTION_STRING As String = "Server=dtpxp-atamdu;database=CapabilityMatrix;uid=test;pwd=test;Max Pool Size = 1079;connection timeout=1000000"

  Const SQL_SELECT_EMPLOYEE As String = _

  "SELECT [ProjectName],[Months],[Years],[Domain],[App],[Category],[Subcategory],[Complexity],[BAET],[ErrorMesg] FROM [delete_tempcategorymaster] "

  Const RECORD_SEPARATOR As String = _

  "----------------------------------------------------------"

  Dim writer As StreamWriter

  Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)

  Try

  Dim adapter As New SqlDataAdapter(SQL_SELECT_EMPLOYEE, con)

  Dim ds As New DataSet

  adapter.Fill(ds)

  writer = New StreamWriter(fileName, False)

  writer.AutoFlush = True

  With ds.Tables(0)

  For row As Integer = 0 To .Rows.Count - 1

  For col As Integer = 0 To .Columns.Count - 1

  writer.WriteLine("{0}: {1}", _

  .Columns(col).ColumnName, .Rows(row)(col))

  Next col

  writer.WriteLine(RECORD_SEPARATOR)

  Next row

  End With

  writer.Close()

  con.Close()

  con.Dispose()

  ' step 2 updating table through stroedprocedure

  Dim SQLCon As New SqlClient.SqlConnection

  Dim SQLCmd As New SqlCommand

  Dim retVal As String

  SQLCon.ConnectionString = "Server=dtpxp-atamdu;database=CapabilityMatrix;uid=test;pwd=test;Max Pool Size = 1079;connection timeout=1000000"

  SQLCon.Open()

  SQLCmd.CommandText = "Usp_ErrorLogEntry" ' Stored Procedure to Call

  SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type

  SQLCmd.CommandTimeout = 3000000

  SQLCmd.Connection = SQLCon 'Active Connection

  Dim PackageName As SqlParameter = SQLCmd.Parameters.Add("@PackageName", "ProcessCategoryData")

  PackageName.Direction = ParameterDirection.Input

  Dim ErrorLogFileName As SqlParameter = SQLCmd.Parameters.Add("@FileName", SqlDbType.Char)

  ErrorLogFileName.Value = NewFileName

  ErrorLogFileName.Direction = ParameterDirection.Input

  SQLCmd.ExecuteNonQuery()

  Dts.TaskResult = Dts.Results.Success

  SQLCon.Close()

  Catch ex As Exception

  Dts.Events.FireError(0, COMPONENT_NAME, ex.Message, "", 0)

  Dts.TaskResult = Dts.Results.Failure

  Finally

  If writer IsNot Nothing Then

  writer.Close()

  End If

  End Try

  Please help me in resolving this issue.

  Thanks.