December 1, 2020 at 3:19 am
This seems to be a common issue throughout my packages. Anywhere I try to access anything in the startup directory. The startup directory is a server URL mapped to Z:\. In this example package. The task that is failing is a script task that verifies the components required are in the startup directory. This is just a script task with a Directory.Exists(StartPath) check.
Z:\ = \\SQLServer\SSIS
Startup directory \\SQLServer\SSIS\GMS\ACS_Returns
Executed as user:
Invalid Input Path Z:\GMS\ACS_Returns\ <-- This is my message
Microsoft (R) SQL Server Execute Package Utility Version 10.50.6560.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 7:43:27 PM
Error: 2020-11-30 19:43:27.66 Code: 0x00000008
Source: House Keeping
Description: The script returned a failure result.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 7:43:27 PM Finished: 7:43:27 PM Elapsed: 0.187 seconds.
The package execution failed. The step failed.
Public Sub Main()
Dim StartPath As String = ""
If Dts.Variables.Contains("StartPath") = True Then
StartPath = Dts.Variables("StartPath").Value
Else
Dts.Events.FireWarning(0, "Start Path not found", "The path or variable is missing", String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
GoTo MainExit
End If
Try
If Directory.Exists(StartPath) Then
Dts.Variables("TotalsQuery_conn").Value = StartPath & "QueryFor_ACS_Export.sql"
Dts.Variables("Export_htm_conn").Value = StartPath & "ACS_Export.htm"
Dts.Variables("Export_xsl_conn").Value = StartPath & "ACS_Export.xsl"
Dts.TaskResult = ScriptResults.Success
Else
Console.WriteLine("Invalid Input Path " & StartPath)
Dts.Events.FireWarning(0, "Start Path not found", StartPath, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End If
Catch ex As Exception
Dim strMsg As String = "Invalid Input Path " & StartPath & vbCrLf & ex.Message
Send_Error_Email(strMsg)
End Try
MainExit:
End Sub
December 1, 2020 at 2:14 pm
This looks like an easy one to me - Z:\ is mapped for you, not mapped for all users on the server. So the SQL Agent service account doesn't have a Z drive.
Use the UNC path and you will likely not have the failures as long as the correct permissions are in place.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply