This was working - SSIS Restore Step

Question

This was working - SSIS Restore Step

jarid.lawson

SSCrazy

Points: 2953
More actions
March 21, 2012 at 3:20 pm

#258938

I created an SSIS package recently that has been working until recently. I actually didn't use it again until today, but as it turns out it "should" have failed yesterday as well. Here's what it does.
Our client sends a .bak file to us in a daily feed. My SSIS package isolates the existing database on our server, drops that database, and restores it from the new .bak file sent that day. It then restores multi-user access, and moves the new .bak file to the archive folder.
When I run it on the files from today and yesterday, it fails on the restore step with the following error msg.:
SSIS package "Import New ClientNameWithheld Data.dtsx" starting.
Error: 0x1 at Restore New Database Copy: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x80040E31): Query timeout expired
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at ST_8bab149e1b654d768005ecadee1df139.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Restore New Database Copy
SSIS package "Import New ClientNameWithheld Data.dtsx" finished: Success.
The step is a Script Task step:
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"
vConnection.Execute(vSQL)
End If
vConnection.Close()
Dts.TaskResult = ScriptResults.Success
I tested the package against each file I have from this client (currently only 8 files, thank God), and the first 6 work just fine. The only differences I'm finding between the files (aside from the names' date / time stamp section) is the file size. I tested the final 3 files via a manual Restore Database query, and they all restored without an issue. Here's the details on these 3:
File 6 - SSIS Working - 347,596 KB - SSMS query version: 41 sec
File 7 - SSIS Not Working - 347,604 KB - SSMS query version: 40 sec
File 8 - SSIS Not Working - 351,708 KB - SSMS query version: 46 sec
My only thought is that I need to set a time out property on the Script Task step, but I don't see where to do that at...or am I looking completely in the wrong direction? Thoughts?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Have you verified the integrity of the backup file? Also, why are you dropping and restoring versus simply overwriting the database? Just curious on the last one...

Jared
CE - Microsoft

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

SQLKnowItAll (3/21/2012)
Have you verified the integrity of the backup file?

If you mean do I know that the files work during a restore, yes they do. What other way would you verify the integrity (not meant to sound like a smart a%s there, it is a serious question)?

Also, why are you dropping and restoring versus simply overwriting the database? Just curious on the last one...

I didn't know about the With Replace option until today. When I originally designed the process it seemed like the logical thing to do. I can edit the package now and remove that step...once I can get the restore to work correctly. Thanks for the suggestion. I am screaming one of my signature quotes in my head right now...bet you can't guess which one...lol:-D

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

"DOH!"
- Homer Simpson[/font]

Jared SSC Guru Points: 61793 More actions · Answer 3

Jared

SSC Guru

Points: 61793

March 21, 2012 at 3:29 pm

#1462454

Check this out.

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Actually, the best way to verify a backup file is to do the restore.

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

I have done both the full restore (listed in first post with time taken to complete the restore) and now Restore VerifyOnly, and all of those tests show the files to be good.

Would something along the lines of setting a timeout property on the Script Task step be a workable solution? If so, where / how do I set that property?

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

"DOH!"
- Homer Simpson[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

If you look at the part I made bold, the query timed out. With that I would say you need a longer timeout period for this to work. Where and how to set it, I will have to look.

jarid.lawson (3/21/2012)
I created an SSIS package recently that has been working until recently. I actually didn't use it again until today, but as it turns out it "should" have failed yesterday as well. Here's what it does.
Our client sends a .bak file to us in a daily feed. My SSIS package isolates the existing database on our server, drops that database, and restores it from the new .bak file sent that day. It then restores multi-user access, and moves the new .bak file to the archive folder.
When I run it on the files from today and yesterday, it fails on the restore step with the following error msg.:
SSIS package "Import New ClientNameWithheld Data.dtsx" starting.
Error: 0x1 at Restore New Database Copy: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x80040E31): Query timeout expired
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at ST_8bab149e1b654d768005ecadee1df139.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Restore New Database Copy
SSIS package "Import New ClientNameWithheld Data.dtsx" finished: Success.
The step is a Script Task step:
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"
vConnection.Execute(vSQL)
End If
vConnection.Close()
Dts.TaskResult = ScriptResults.Success
I tested the package against each file I have from this client (currently only 8 files, thank God), and the first 6 work just fine. The only differences I'm finding between the files (aside from the names' date / time stamp section) is the file size. I tested the final 3 files via a manual Restore Database query, and they all restored without an issue. Here's the details on these 3:
File 6 - SSIS Working - 347,596 KB - SSMS query version: 41 sec
File 7 - SSIS Not Working - 347,604 KB - SSMS query version: 40 sec
File 8 - SSIS Not Working - 351,708 KB - SSMS query version: 46 sec
My only thought is that I need to set a time out property on the Script Task step, but I don't see where to do that at...or am I looking completely in the wrong direction? Thoughts?

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

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

-- Sorry for the multiple posts. I partially re-asked this question focusing on just the how to set the Timeout property. I'm posting the solution in both posts just in case you found this post instead of the Timeout specific post.

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

"DOH!"
- Homer Simpson[/font]