Issue with Script Task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Issue with Script Task

Author

Topic

m__x
Starting Member

3 Posts

Posted - 2008-11-24 : 09:57:41

Hi all,

I'm new to SSIS and to this forum. I use SQL Server 2005.

I've been struggling for nearly one month with a problem with a script task. The main reason that let me unsuccessful with solving it, it's because the problem occurs only from time to time. I'm really without any idea at the moment...
The code is in VB, and I use it in order to collect data from databases, generate a HTML email, and send it. I suspect that the problem is because I use this code through SSIS, because otherwise it works perfectly fine.

Is there any chance that this errors let you think about anything helpful? I will be really grateful for any kind of help. Ask if you need more information about my issue!

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

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ScriptTask_930d2696681e4bd68e99b47e30550408.ScriptMain.Main()

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:25:22

Timeout, heh?
I think we better see the actual code producing the error, not the error text.

E 12°55'05.63"
N 56°04'39.26"

m__x
Starting Member

3 Posts

Posted - 2008-11-24 : 10:46:06

Here's the code.
Thx for your time!

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Option Strict Off
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain

Public Sub Main()

'Find the current quarter
Dim currentTime As System.DateTime = System.DateTime.Now
Dim Current_Quarter As String = currentTime.Month.ToString

If Current_Quarter >= 1 And Current_Quarter <= 3 Then
Current_Quarter = "Q1"
ElseIf Current_Quarter >= 4 And Current_Quarter <= 6 Then
Current_Quarter = "Q2"
ElseIf Current_Quarter >= 7 And Current_Quarter <= 9 Then
Current_Quarter = "Q3"
ElseIf Current_Quarter >= 10 And Current_Quarter <= 12 Then
Current_Quarter = "Q4"
End If

Dim htmlMessageFrom As String = "<xxx@xxx.com>"
Dim htmlMessageTo As String = Dts.Variables("OwnerMail").Value.ToString
Dim htmlMessageBCC As String = "xxx@xxx.com"
Dim htmlMessageSubject As String = "SUBJECT"
Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP Connection Manager").AcquireConnection(Dts.Transaction), String)
Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)
Dim htmlMessageBody As String

'These are the different tables for each job
Dim ArrayJob1Body As String = String.Empty
Dim ArrayJob2Body As String = String.Empty
Dim ArrayJob3Body As String = String.Empty
Dim ArrayJob4Body As String = String.Empty
Dim ArrayJob5Body As String = String.Empty

'We will use these 4 variables to determine which sections must be dislayed in the email
Dim Array1Exists As Integer = Dts.Variables("RowCount1").Value()
Dim Array2Exists As Integer = Dts.Variables("RowCount2").Value()
Dim Array3Exists As Integer = Dts.Variables("RowCount3").Value()
Dim Array4Exists As Integer = Dts.Variables("RowCount4").Value()
Dim Array5Exists As Integer = Dts.Variables("RowCount5").Value()

'This variable is to check if we need to send the congrats message to the opp owner
Dim Pipeline_OK As Integer = CInt(Dts.Variables("Job1CheckResult").Value)

'These are all the different sections of the email, in the correct order
Dim Logo As String = "<img xxx>"
Dim OwnerName As String = "" + Dts.Variables("OwnerName").Value.ToString + ", "
Dim PipelineInfo As String = "xxx"
Dim Footer As String = "xxx"

'Specify your OLE DB connection settings
Const CONNECTION_STRING As String = "Data Source=SOURCE;UID=LOGIN;PWD=PASSWORD;Connect Timeout=400;Initial Catalog=DB;Integrated Security=false;"

'JOB ID 1
Const SQL_Select_OppsListJob1 As String = "xxxQUERY"

'The parameters used for connecting, retrieving and storing the data from the Staging Area
Dim con As SqlConnection = New SqlConnection(CONNECTION_STRING)
Dim adapter As New SqlDataAdapter(SQL_Select_OppsListJob1, con)
Dim ds As New DataSet
adapter.Fill(ds)

With ds.Tables(0)

'To build the JOB1 table, we loop through the OppsListJob1 table
Xxx
Xxx
Xxx

End With

'ArrayJob1Header contains the title of the table and defines the table headings
Xxx

'We concatenate the header with the body
Dim ArrayJob1 As String = String.Concat(ArrayJob1Header, ArrayJob1Body)
'And then concatenate it with the footer
ArrayJob1 = ArrayJob1.Concat(ArrayJob1, ArrayJob1Footer)
'ArrayJob1 now contains the whole table for JOB 1

'JOB ID 2 pretty much the same as JOB ID 1
'JOB ID 3
'JOB ID 4
'JOB ID 5

'Copy the template.txt file into a String
Dim fileText As String = My.Computer.FileSystem.ReadAllText("xxx.txt")

'Total Pipeline is 100% ==> printscreen the congrats message
If Pipeline_OK > 0 Then
fileText = fileText.Replace("xx_Logo_xx", Logo)
fileText = fileText.Replace("xx_RecipientFirstName_xx", OwnerName)
fileText = fileText.Replace("xx_PipelineInfo_xx", "")
fileText = fileText.Replace("xx_HtmlBodyContent1_xx", "xxx")
fileText = fileText.Replace("xx_HtmlBodyContent2_xx", "")
fileText = fileText.Replace("xx_HtmlBodyContent3_xx", "")
fileText = fileText.Replace("xx_HtmlBodyContent4_xx", "")
fileText = fileText.Replace("xx_Footer_xx", Footer)

htmlMessageBody = fileText
SendMailMessage(htmlMessageFrom, htmlMessageTo, htmlMessageBCC, htmlMessageSubject, htmlMessageBody, True, smtpServer)

'We now delete the person in JobCompleted so he only receives the congrats message once
Dim SQL_Delete_JobCompleted As String = "xxxQUERY"
Dim adapter7 As New SqlDataAdapter(SQL_Delete_JobCompleted, con)
Dim ds7 As New DataSet
adapter7.Fill(ds7)

End If

If (Array1Exists > 0 Or Array2Exists > 0 Or Array3Exists > 0 Or Array4Exists > 0 Or Array5Exists > 0) Then

If Array1Exists > 0 Then 'If there is at least one opportunity in JOB1

fileText = fileText.Replace("xx_Logo_xx", Logo)
fileText = fileText.Replace("xx_RecipientFirstName_xx", OwnerName)
fileText = fileText.Replace("xx_PipelineInfo_xx", PipelineInfo)
fileText = fileText.Replace("xx_HtmlBodyContent1_xx", ArrayJob1)
fileText = fileText.Replace("xx_Footer_xx", Footer)

Else 'Replace with a blank

fileText = fileText.Replace("xx_HtmlBodyContent1_xx", "")

End If

If Array2Exists > 0 Then
etc
Else

fileText = fileText.Replace("xx_HtmlBodyContent2_xx", "")

End If

--etc: replacing the text from the file.

htmlMessageBody = fileText
SendMailMessage(htmlMessageFrom, htmlMessageTo, htmlMessageBCC, htmlMessageSubject, htmlMessageBody, True, smtpServer)

End If

Dts.TaskResult = Dts.Results.Success

End Sub

--------------------------------

Private Sub SendMailMessage(ByVal From As String, ByVal SendTo As String, _
ByVal BCC As String, ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)

Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

htmlMessage = New MailMessage(From, SendTo, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml
htmlMessage.Bcc.Add(BCC)

mySmtpClient = New SmtpClient(Server)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)

End Sub

End Class

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:48:12

On which line is the code taking a timeout?

On SendMailMessage? My.Computer.FileSystem.ReadAllText? adapter.Fill(ds)?

E 12°55'05.63"
N 56°04'39.26"

m__x
Starting Member

3 Posts

Posted - 2008-11-24 : 11:13:13

As it's run from SSIS, no line error is returned. THAT'S THE PROBLEM. I only know it's something within the script task.
My only way to find what's wrong with it is to run it partly, hoping that the timeout is still there. Usually, I can run the package 1 or 2-3 times before, suddenly and amazingly, all work perfectly fine. I need to pull the package from the server again in order to start the testing/debugging another time, for another 2-3 times...

So far (after one month), I still don't know what's wrong.

If this package could be run manually, it wouldn't be such a trouble. I'd just run it untill it works. But the fact is that I have to SCHEDULE it. It must work every single time that it's run.

This post isn't really useful :( but maybe it can clarify the circumstances.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources