Setting timeout option for Script Task in SSIS

Question

Post reply

Setting timeout option for Script Task in SSIS

jarid.lawson

SSCrazy

Points: 2953
More actions
March 22, 2012 at 9:46 am

#391845

I have a script task that executes a dynamically created SQL restore statement. This has worked without a problem for the first 6 runs, but then the .bak files grew too large to run in under 30 sec.
I have looked everywhere I can think of for a timeout option. I have reset the Connection Manager's General Timeout to 500 (from what I understand that is in seconds). None of this has worked yet.
Where / how do I set this value (or can I)?
BIDS Version Info:
Microsoft Visual Studio 2008
Version 9.0.30729.4462 QFE
Microsoft .Net Framework
Version 3.5 SP1
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

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

jarid.lawson SSCrazy Points: 2953 More actions · Answer 1

I found the solution on another forum.

Script Task code:

Public Sub Main()

Dim vFTP_Loc As String

Dim vFile As Object

Dim vFileObject As Object

Dim vFolder As Object

Dim FSO As Object

Dim vFileCount As Integer

Dim vConnection As Object

Dim vSQL As String

vFTP_Loc = "D:\Company\FTP\ClientNameWithheld\"

FSO = CreateObject("Scripting.FileSystemObject")

vFolder = FSO.GetFolder(vFTP_Loc)

vFileCount = 0

For Each vFile In vFolder.Files

vFileCount = vFileCount + 1

vFileObject = vFile

Next

vConnection = CreateObject("ADODB.Connection")

vConnection.Open = "Provider = SQLNCLI10.1;Data Source = ServerNameWithheld;Initial Catalog = Master;Integrated Security = SSPI"

If vFileCount > 1 Then

vSQL = "EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB Admin',@recipients = 'jarid.lawson@lgmfinance.com',@body_format = 'TEXT',@body = 'Multiple files found in ClientNameWithheld FTP folder. Automated import terminated without updating.',@subject = 'ClientNameWithheld Update Terminated Unsuccessfully';"

vConnection.Execute(vSQL)

Dts.TaskResult = ScriptResults.Failure

ElseIf vFileCount = 0 Then

vSQL = "EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB Admin',@recipients = 'jarid.lawson@lgmfinance.com',@body_format = 'TEXT',@body = 'No files found in ClientNameWithheld FTP folder. Automated import terminated without updating.',@subject = 'ClientNameWithheld Update Terminated Unsuccessfully';"

vConnection.Execute(vSQL)

Dts.TaskResult = ScriptResults.Failure

Else

vSQL = "Restore Database DatabaseNameWithheld From Disk = '" & vFTP_Loc & vFileObject.Name & "' With Replace"

'Solution to the issue

'********************************************

vConnection.CommandTimeout = 500

'********************************************

vConnection.Execute(vSQL)

End If

vConnection.Close()

Dts.TaskResult = ScriptResults.Success

End Sub

I love learning new code. I was not aware of the CommandTimeout property. I believe this is a VB property, so I guess I need to focus more on my VB skills.

Thanks to all that helped!

-- Posted across multiple forums

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]